Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: HELP! Index Debate!

Re: HELP! Index Debate!

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 04 Aug 2003 19:49:22 -0800
Message-ID: <F001.005C8E35.20030804194922@fatcity.com> 





Yes, the index can be used, but only the portion up to the first missing 
column, or a column where the predicate is not an equal predicate, is used. 
Those are so-called access predicates. The remaining predicates are 
resolved after the row is fetched from the datablock - a so-called filter 
predicate.



I offer some indirect evidence. Consider table test (a number, b number, c 
number, d number, e varchar2(500));


The table is loaded as follows:


insert into test select mod(trunc(dbms_random.value*100,0),5)+1

, mod(trunc(dbms_random.value*100,0),5)+1
, mod(trunc(dbms_random.value*100,0),5)+1
, mod(trunc(dbms_random.value*100,0),5)+1
, rpad('a',50,'*')




from dba_objects where rownum <= 25005


/


commit;


create index test_a on test (a, b, c, d);
analyze table test compute statistics;



I then ran a sequemce of test sql:



alter tablespace users offline;


alter tablespace users online;


alter session set events '10046 trace name context forever, level 8';
select e from test


where a=1

   and b=1
   and c=1
   and d=1




/


alter session set events '10046 trace name context off';



alter tablespace users offline;


alter tablespace users online;


alter session set events '10046 trace name context forever, level 8';
select e from test


where a=1


   and b=1


   and c=1


   and d<2


/


alter session set events '10046 trace name context off';



alter tablespace users offline;


alter tablespace users online;


alter session set events '10046 trace name context forever, level 8';
select e from test


where a=1


   and b=1


   and c<2


   and d=1


/


alter session set events '10046 trace name context off';



alter tablespace users offline;


alter tablespace users online;


alter session set events '10046 trace name context forever, level 8';
select e from test


where a=1


   and b<2


   and c=1


   and d=1


/


alter session set events '10046 trace name context off';



and



alter tablespace users offline;


alter tablespace users online;


alter session set events '10046 trace name context forever, level 8';
select e from test


where a<2

   and b=1
   and c=1
   and d=1




/


alter session set events '10046 trace name context off';



Note that because of the way the data is loaded, columns a, b, c, and d 
only have values of 1,2,3,4, or 5 and therefore all the sql return the same 
rows. The tablespace offline/online is there to invalidate any buffers of 
the table and the index in the buffer pool (both are in tablespace users) 
so that every query has to start from scratch.



Here are the trace results. Tablespace users consists of datafile #4. All 
extents are 8 blocks (uniform LMT) and blocks 34985-35152 makes up the 
index and blocks 24721-24728, 25337-26296, and 32825-34984 make up the table:



PARSING IN CURSOR #1 len=59 dep=0 uid=23 oct=3 lid=23 tim=2925349191 
hv=596441455 ad='511d4e88'


select e from test


where a=1

   and b=1
   and c=1
   and d=1




END OF STMT



PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2925349191

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2925349191
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34986 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34987 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25345 p3=1


FETCH #1:c=0,e=0,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=2925349191

WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25688 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25740 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25839 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25910 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25925 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25948 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26018 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26240 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33350 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33489 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33840 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33903 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34032 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34114 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34207 p3=1


FETCH #1:c=0,e=0,p=15,cr=16,cu=0,mis=0,r=15,dep=0,og=4,tim=2925349191

WAIT #1: nam='SQL*Net message from client' ela= 5 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34235 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34280 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34294 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34295 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34304 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34379 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34473 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34578 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34732 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34782 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34821 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34944 p3=1


FETCH #1:c=0,e=0,p=12,cr=13,cu=0,mis=0,r=13,dep=0,og=4,tim=2925349196
WAIT #1: nam='SQL*Net message from client' ela= 11 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=29 pid=0 pos=0 obj=87891 op='TABLE ACCESS BY INDEX ROWID 
TEST '



STAT #1 id=2 cnt=30 pid=1 pos=1 obj=100890 op='INDEX RANGE SCAN '



The index has a blevel of 1, Oracle reads blocks 34986 (root) and 34987 
(leaf) of the index, the rest are data reads.



PARSING IN CURSOR #1 len=59 dep=0 uid=23 oct=3 lid=23 tim=2925352732 
hv=1677429270 ad='511c575c'


select e from test


where a=1


   and b=1


   and c=1


   and d<2


END OF STMT



PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2925352732

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2925352732
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34986 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34987 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25345 p3=1


FETCH #1:c=0,e=0,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=2925352732

WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25688 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25740 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25839 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25910 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25925 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25948 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26018 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26240 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33350 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33489 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33840 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33903 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34032 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34114 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34207 p3=1


FETCH #1:c=0,e=0,p=15,cr=16,cu=0,mis=0,r=15,dep=0,og=4,tim=2925352732

WAIT #1: nam='SQL*Net message from client' ela= 4 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34235 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34280 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34294 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34295 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34304 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34379 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34473 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34578 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34732 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34782 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34821 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34944 p3=1


FETCH #1:c=0,e=0,p=12,cr=13,cu=0,mis=0,r=13,dep=0,og=4,tim=2925352736
WAIT #1: nam='SQL*Net message from client' ela= 13 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=29 pid=0 pos=0 obj=87891 op='TABLE ACCESS BY INDEX ROWID 
TEST '



STAT #1 id=2 cnt=30 pid=1 pos=1 obj=100890 op='INDEX RANGE SCAN '



No difference yet. But that is to be expected since Oracke can still make 
use of the full predicate set.



PARSING IN CURSOR #1 len=59 dep=0 uid=23 oct=3 lid=23 tim=2925354608 
hv=3409620506 ad='511c84b0'


select e from test


where a=1


   and b=1


   and c<2


   and d=1


END OF STMT



PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2925354608

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2925354608
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34986 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34987 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25345 p3=1


FETCH #1:c=0,e=0,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=2925354608

WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25688 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25740 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25839 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25910 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25925 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25948 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26018 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26240 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33350 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33489 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33840 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33903 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34032 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34114 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34207 p3=1


FETCH #1:c=1,e=1,p=15,cr=16,cu=0,mis=0,r=15,dep=0,og=4,tim=2925354609

WAIT #1: nam='SQL*Net message from client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34235 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34280 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34294 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34295 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34304 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34379 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34473 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34578 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34732 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34782 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34821 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34944 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34988 p3=1


FETCH #1:c=0,e=0,p=13,cr=14,cu=0,mis=0,r=13,dep=0,og=4,tim=2925354612
WAIT #1: nam='SQL*Net message from client' ela= 13 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=29 pid=0 pos=0 obj=87891 op='TABLE ACCESS BY INDEX ROWID 
TEST '



STAT #1 id=2 cnt=30 pid=1 pos=1 obj=100890 op='INDEX RANGE SCAN '



One more physical and logical read than before: the extra read of index 
block 34988. No further data block reads are required. All necessary blocks 
are already in the buffer pool. As pointed out earlier, the resultset is 
the same for all queries and if you compare the data block reads to those 
of the first query, all needed block containing rows of the resultset have 
already been read.



PARSING IN CURSOR #1 len=59 dep=0 uid=23 oct=3 lid=23 tim=2925356067 
hv=3247637312 ad='511c7d20'


select e from test


where a=1


   and b<2


   and c=1


   and d=1


END OF STMT



PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2925356067

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2925356067
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34986 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34987 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25345 p3=1


FETCH #1:c=0,e=0,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=2925356067

WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25688 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25740 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25839 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25910 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25925 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25948 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26018 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26240 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33350 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33489 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33840 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33903 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34032 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34114 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34207 p3=1


FETCH #1:c=0,e=0,p=15,cr=16,cu=0,mis=0,r=15,dep=0,og=4,tim=2925356067

WAIT #1: nam='SQL*Net message from client' ela= 4 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34235 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34280 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34294 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34295 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34304 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34379 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34473 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34578 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34732 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34782 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34821 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34944 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34988 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34989 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34990 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34991 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34992 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34993 p3=1


FETCH #1:c=0,e=0,p=18,cr=19,cu=0,mis=0,r=13,dep=0,og=4,tim=2925356071
WAIT #1: nam='SQL*Net message from client' ela= 12 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=29 pid=0 pos=0 obj=87891 op='TABLE ACCESS BY INDEX ROWID 
TEST '



STAT #1 id=2 cnt=30 pid=1 pos=1 obj=100890 op='INDEX RANGE SCAN '



6 additional index block reads compared to the sql with all equality 
predicates. No additional data block reads for the same reason as before.



PARSING IN CURSOR #1 len=59 dep=0 uid=23 oct=3 lid=23 tim=2925357211 
hv=2123160879 ad='511e4e7c'


select e from test


where a<2

   and b=1
   and c=1
   and d=1




END OF STMT



PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2925357211

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2925357211
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34986 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34987 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25345 p3=1


FETCH #1:c=0,e=0,p=3,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=2925357211

WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25688 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25740 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25839 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25910 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25925 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=25948 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26018 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=26240 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33350 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33489 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33840 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=33903 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34032 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34114 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34207 p3=1


FETCH #1:c=0,e=0,p=15,cr=16,cu=0,mis=0,r=15,dep=0,og=4,tim=2925357211

WAIT #1: nam='SQL*Net message from client' ela= 4 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34235 p3=1
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34280 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34294 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34295 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34304 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34379 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34473 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34578 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34732 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34782 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34821 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=4 p2=34944 p3=1
WAIT #1: nam='db file scattered read' ela= 1 p1=4 p2=34988 p3=11
WAIT #1: nam='db file scattered read' ela= 1 p1=4 p2=34999 p3=11
WAIT #1: nam='db file scattered read' ela= 0 p1=4 p2=35010 p3=11


FETCH #1:c=0,e=2,p=45,cr=47,cu=0,mis=0,r=13,dep=0,og=4,tim=2925357217
WAIT #1: nam='SQL*Net message from client' ela= 10 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=29 pid=0 pos=0 obj=87891 op='TABLE ACCESS BY INDEX ROWID 
TEST '



STAT #1 id=2 cnt=30 pid=1 pos=1 obj=100890 op='INDEX RANGE SCAN '



33 more physical reads and 34 more logical reads than the query with all 
equal predicates.



There are also two surprising facts in this trace - suprising at least for me:



    
  
  1.   Oracle switches to a fast scan of the index - see the scattered reads.
  
  2.   the scattered reads read across extent boundaries. The extents happen to 
be contiguous. As mentioned, all extents consist of 8 blocks, but the 
scattered reads are for 11 blocks each. Why only 11 I don't know, since 
db_file_multiblock_read_count is set to 32.






I'll also look for corroborating evidence in Tom Kyte's Expert one-on-one, 
but it's in the office right now.



At 06:19 AM 8/4/2003 -0800, you wrote:


>Wolfgang,

>

> > as long as the leading columns are present in the where clause with an

> > equal predicate, the index can be used. The first omission or non-equal

> > predicate breaks the chain and only the part of the index up to that

>column

> > can be used.

>

>Could you please elaborate what you meant by that? My understanding (and

>brief testing results) are that composite indexes can be used with

>inequality predicates (<, >, between, like) and even a missing predicate in

>between. Do you mean that normal index branch block traversing mechanism

>can't be used starting with omitted or non-equal predicate and starting from

>them leaf block linked list is read up to a value is found which doesn't

>match the last equality predicate?






Wolfgang Breitling


Centrex Consulting Corporation


http://www.centrexcc.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Mon Aug 04 2003 - 22:49:22 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US