Re: How Oracle find rows to delete in an index

"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:<41225165_at_post.usenet.com>...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
> Hi Bruno
>
> "Bruno Jargot" <see_at_reply-to.invalid> wrote in message
> news:1giohei.1r2gls4a95a8sN%see_at_reply-to.invalid...
>
> > Will Oracle do a range scan in the index I2 after finding the value of
> > C2 in the table ?
>
> This is the only way to locate an index entry... a simple tests confirm this
> sequence as well...
>
> >>>>>>>>> connect and create a table with two indexes
>
> SQL> conn system/manager
> Connected.
> SQL> create table t as select * from scott.emp;
> Table created.
> SQL> alter table t add constraint t_pk primary key (empno);
> Table altered.
> SQL> create index t_i on t (ename);
> Index created.
>
> >>>>>>>>> flush buffer cache to force physical I/O
>
> SQL> alter system flush buffer_cache;
> System altered.
>
> >>>>>>>>> enable SQL trace to get I/O details
>
> SQL> alter session set events '10046 trace name context forever, level 8';
> Session altered.
>
> >>>>>>>>> delete 1 row, disable SQL trace and rollback changes
>
> SQL> delete t where empno = 7788;
> 1 row deleted.
> SQL> alter session set events '10046 trace name context off';
> Session altered.
> SQL> rollback;
> Rollback complete.
>
> >>>>>>>>> excerpt of the trace file (notice that the execution plan confirms
> the index scan to access the table)
>
> =====================
> PARSING IN CURSOR #1 len=27 dep=0 uid=5 oct=7 lid=5 tim=1067154837068255
> hv=1024503997 ad='6d5af454'
> delete t where empno = 7788
> END OF STMT
> PARSE
> #1:c=60000,e=309402,p=21,cr=68,cu=0,mis=1,r=0,dep=0,og=1,tim=106715483706824
> 6
> WAIT #1: nam='db file sequential read' ela= 101 p1=1 p2=63274 p3=1
> WAIT #1: nam='db file sequential read' ela= 36 p1=1 p2=63266 p3=1
> WAIT #1: nam='db file sequential read' ela= 38 p1=1 p2=63265 p3=1
> WAIT #1: nam='db file sequential read' ela= 37 p1=1 p2=63282 p3=1
> EXEC #1:c=0,e=1122,p=4,cr=1,cu=8,mis=0,r=1,dep=0,og=1,tim=1067154837069478
> WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1413697536 p2=1 p3=0
> WAIT #1: nam='SQL*Net message from client' ela= 40819 p1=1413697536 p2=1
> p3=0
> STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='DELETE (cr=1 pr=4 pw=0 time=983
> us)'
> STAT #1 id=2 cnt=1 pid=1 pos=1 obj=65211 op='INDEX UNIQUE SCAN T_PK (cr=1
> pr=1 pw=0 time=224 us)'
> =====================
>
> >>>>>>>>> show first/last block for the 3 segments
>
> SQL> SELECT segment_name, block_id first_block, block_id+blocks-1 last_block
> 2 FROM dba_extents
> 3 WHERE segment_name IN ('T', 'T_PK', 'T_I') AND owner = user;
>
> SEGMENT_NAME FIRST_BLOCK LAST_BLOCK
> ------------------------------ ----------- ----------
> T 63265 63272
> T_PK 63273 63280
> T_I 63281 63288
>
> >>>>>>>>> analysis
>
> - first I/O: block=63274 --> T_PK
> - second and third I/O: block=63266,63265 --> T
> - fourth I/O: block=63282 --> T_I
>
>
> Chris
>
 

Christian posted a (very nice) trace of what Oracle does but in simple terms when Oracle deletes a row from a table it has access to all the column values on the to be deleted rows. Using the column values provided by the table row Oracle scans the index(es) and finds the associated index entries which it them removes.

HTH -- Mark D Powell -- Received on Tue Aug 17 2004 - 17:38:36 CDT