|
Oracle does not need to scan the whole index to delete the indexed
values. This can be tested easily: SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for 32-bit Windows: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production SQL> @z SQL> DROP TABLE TEST; Table dropped. SQL> CREATE TABLE TEST (idx_col1 NUMBER,idx_col2 NUMBER, padding VARCHAR2(1000)); Table created. SQL> SQL> INSERT INTO TEST 2 SELECT LEVEL, LEVEL, RPAD ('*', 1000, '*') 3 FROM DUAL 4 CONNECT BY LEVEL <= 10000; 10000 rows created. SQL> SQL> CREATE INDEX test_idx1 ON TEST(idx_col1); Index created. SQL> CREATE INDEX test_idx2 ON TEST(idx_col2); Index created. SQL> SQL> EXEC dbms_Stats.gather_table_stats('test','test',estimate_percent=>100,degree=>4,cascade=>true); PL/SQL procedure successfully completed. SQL> SQL> COL row_id new_value row_id SQL> SQL> SELECT row_id 2 FROM (SELECT ROWID row_id 3 FROM TEST 4 ORDER BY DBMS_RANDOM.VALUE) 5 WHERE ROWNUM < 2; ROW_ID ------------------ AAAK2xAAGAAACZCAAC SQL> SQL> SELECT idx_col1, idx_col2 2 FROM TEST 3 WHERE ROWID = '&row_id'; old 3: WHERE ROWID = '&row_id' new 3: WHERE ROWID = 'AAAK2xAAGAAACZCAAC' IDX_COL1 IDX_COL2 ---------- ---------- 129 129 SQL> SQL> DELETE FROM TEST 2 WHERE ROWID = '&row_id'; old 2: WHERE ROWID = '&row_id' new 2: WHERE ROWID = 'AAAK2xAAGAAACZCAAC' 1 row deleted. SQL> SQL> SELECT * 2 FROM TABLE (DBMS_XPLAN.display_cursor ()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- SQL_ID 3szgts95ndbj9, child number 0 ------------------------------------- DELETE FROM TEST WHERE ROWID = 'AAAK2xAAGAAACZCAAC' Plan hash value: 2251118889 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | DELETE STATEMENT | | | | 1 (100)| | | 1 | DELETE | TEST | | | | | | 2 | TABLE ACCESS BY USER ROWID| TEST | 1 | 20 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ 14 rows selected. SQL> And the relevant part of the trace file: PARSING IN CURSOR #35 len=57 dep=0 uid=177 oct=7 lid=177 tim=484572684976 hv=1989778350 ad='1e9e0c2c' DELETE FROM TEST WHERE ROWID = 'AAAK2uAAGAAACjMAAE' END OF STMT PARSE #35:c=0,e=653,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=484572684970 EXEC #35:c=0,e=161,p=0,cr=1,cu=6,mis=0,r=1,dep=0,og=1,tim=484572685301 STAT #35 id=1 cnt=1 pid=0 pos=1 obj=0 op='DELETE TEST (cr=1 pr=0 pw=0 time=122 us)' STAT #35 id=2 cnt=1 pid=1 pos=1 obj=44462 op='TABLE ACCESS BY USER ROWID TEST (cr=1 pr=0 pw=0 time=21 us)' Harel Safra troach@xxxxxxxxx wrote: Since a btree index is organized by columns, not rowid (assuming its a btree index) oracle need to full scan it for that rowid. Since rowid points to the row in the table and not the index, it has no choice but to full scan the index. Someone please correct me if I'm wrong?-- //www.freelists.org/webpage/oracle-l |
