Re: How Oracle find rows to delete in an index

From: Christian Antognini <christian.antognini_at_trivadis.com>
Date: Tue, 17 Aug 2004 20:41:37 +0200
Message-ID: <41225165@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

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Received on Tue Aug 17 2004 - 13:41:37 CDT