hint to use global index instead of local indexes?

From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 20 Jul 2011 20:18:39 -0500
anyone know if there's a way to hint oracle to use a global index instead of
local indexes?

i have an extract from a trace file here... i think that the nested loop is
getting 649772 rows as input.  so the outer part of the loop should be
executing this many times.  the outer is doing an index range scan on a
partitioned index with a single value for search key (the index is
non-unique, and this key is the leading field of the 5-field index).  a
typical index partition has 6986 leaf blocks and there are 830761 unique
id's for this search key, so I don't think it should ever have to scan more
than one leaf block to check for the existance of this ID.  (it's driven by
a NOT EXISTS clause.)

however i noticed that the index range scan is doing 8826189 consistent
reads for 649772 loops.  since the local indexes have a max blevel of 2, i
hypothesized that perhaps it needs to scan two or three index partitions to
check for the search key.  however the global index has a blevel of 3 -- so
i'm thinking, why doesn't oracle just use the global index?

the time in this index range scan accounts for 50% of a 6 hour batch run
(about 9% of these I/Os go to disk).  if using the global index reduces
logical gets per row from 7 to 4 then that could take an hour or two off the
runtime.  am i missing anything?  why doesn't oracle use the global index
instead of scanning a few local indexes (if that's what it's doing)?  can i
hint it?

-Jeremy

PS... here are the relevant lines from the trace:

STAT #1 id=35 cnt=1305528 pid=34 pos=1 obj=0 op='NESTED LOOPS  (cr=38716413
pr=781204 pw=0 time=10852870565 us)'
STAT #1 id=36 cnt=649772 pid=35 pos=1 obj=0 op='BUFFER SORT (cr=29890224
pr=0 pw=0 time=456569392 us)'
STAT #1 id=37 cnt=649772 pid=36 pos=1 obj=0 op='PX RECEIVE  (cr=29890224
pr=0 pw=0 time=433619359 us)'
STAT #1 id=38 cnt=649772 pid=37 pos=1 obj=0 op='PX SEND BROADCAST :TQ10000
(cr=29890224 pr=0 pw=0 time=431151440 us)'
STAT #1 id=39 cnt=649772 pid=38 pos=1 obj=0 op='NESTED LOOPS  (cr=29890224
pr=0 pw=0 time=429355854 us)'
STAT #1 id=40 cnt=649772 pid=39 pos=1 obj=1234 op='TABLE ACCESS BY INDEX
ROWID CLNT_PARAMS (cr=1300256 pr=0 pw=0 time=28789235 us)'
STAT #1 id=41 cnt=649772 pid=40 pos=1 obj=2345 op='INDEX RANGE SCAN
CLNT_PARAMS_IND (cr=650484 pr=0 pw=0 time=11759527 us)'
STAT #1 id=42 cnt=649772 pid=39 pos=2 obj=0 op='PARTITION RANGE ALL
PARTITION: 1 33 (cr=28589968 pr=0 pw=0 time=397961045 us)'
STAT #1 id=43 cnt=649772 pid=42 pos=1 obj=0 op='PARTITION LIST ALL
PARTITION: 1 LAST (cr=28589968 pr=0 pw=0 time=376194117 us)'
STAT #1 id=44 cnt=649772 pid=43 pos=1 obj=3456 op='INDEX RANGE
SCAN BIG_TABLE_INDEX_2 PARTITION: 1 44 (cr=28589968 pr=0 pw=0 time=300442749
us)'
STAT #1 id=45 cnt=632410 pid=35 pos=2 obj=0 op='PX PARTITION LIST ITERATOR
PARTITION: KEY KEY (cr=8826189 pr=781204 pw=0 time=10432193366 us)'
STAT #1 id=46 cnt=632410 pid=45 pos=1 obj=4567 op='INDEX RANGE
SCAN BIG_TABLE_INDX PARTITION: KEY 44 (cr=8826189 pr=781204 pw=0
time=10364472880 us)'

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
System name: AIX
Release: 3
Version: 5


-- 
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago
hint to use global index instead of local indexes?

Other related posts:
