You were right that I wasn't thinking that there would be visits to the table for each index key pair retrieval. How could I not understand--great example. I feel a little silly for not figuring it out on my own...thanks for the hand holding. Hopefully I wasn't the only benefactor! :) Dan ----- Original Message ---- From: Alberto Dell'Era <alberto.dellera@xxxxxxxxx> To: dannorris@xxxxxxxxxxxxx Cc: Oracle L <oracle-l@xxxxxxxxxxxxx> Sent: Thursday, January 10, 2008 5:09:49 PM Subject: Re: explain plan, can you explain this? > Alberto's reponse > (//www.freelists.org/archives/oracle-l/01-2008/msg00232.html) makes the > most sense as far as explaining some of the why, but I don't know that I > agree with the possible solution. I don't think that adding columns to the > index would make a big difference since finding the right index_start should > be all that's needed. But the problem is that Oracle must read a lot of TABLE blocks until it has explored all the index keys up to the right index_start ... The annotated script below (that should reproduce most of your scenario, including the very important condition "This query always has to return a single row. For a given program_id, index_start and index_end define ranges that do not overlap") should both illustrate the problem, and demonstrate the effectiveness of one of the suggested indexes. Results from 10.2.0.3. create table some_lookup ( program_id int not null, index_start int not null, index_end int not null, ma_id int, others varchar2(1000 char) ); exec dbms_random.seed (0); insert into some_lookup (program_id, index_start, index_end, ma_id, others) select 0, (rownum-1)*10, (rownum-1)*10+2, (rownum-1)*10, rpad('x', 1000) from dual connect by level <= 100 order by dbms_random.random / create unique index uk_some_lookup on some_lookup (program_id, index_start); -- create unique index iot_like_idx on some_lookup (program_id, index_end, index_start, ma_id); select index_name, blevel, leaf_blocks from user_indexes where table_name = 'SOME_LOOKUP'; -- INDEX_NAME BLEVEL LEAF_BLOCKS -- -------------------- ---------- ----------- -- UK_SOME_LOOKUP 0 1 -- note : the index is composed by 1 single block exec dbms_stats.gather_table_stats (user, 'some_lookup', cascade=>true, method_opt=>'for all columns size 1', estimate_percent=>null); select program_id, index_start, index_end, ma_id from some_lookup order by 1, 2; -- PROGRAM_ID INDEX_START INDEX_END MA_ID -- ---------- ----------- ---------- ---------- -- 0 0 2 0 -- 0 10 12 10 <-- :i = 11 selects this -- 0 20 22 20 -- ... -- 0 970 972 970 -- 0 980 982 980 -- 0 990 992 990 <-- :i = 991 selects this variable p number variable i number exec :p := 0; :i := 11; SELECT ma_id FROM some_lookup WHERE program_id = :p AND :i BETWEEN index_start and index_end; select * from table (dbms_xplan.display_cursor); ---------------------------------------------------------------- --| Id | Operation | Name | Rows | ---------------------------------------------------------------- --| 0 | SELECT STATEMENT | | | --|* 1 | TABLE ACCESS BY INDEX ROWID| SOME_LOOKUP | 2 | --|* 2 | INDEX RANGE SCAN | UK_SOME_LOOKUP | 2 | ---------------------------------------------------------------- -- -- 1 - filter("INDEX_END">=:I) -- 2 - access("PROGRAM_ID"=:P AND "INDEX_START"<=:I) -- note: accesses (walks) the index from (program_id, index_start) = (0,2) to (0,:i); -- for each matching index key, gets a table block and filters by index_end >= :i set autotrace traceonly statistics SELECT ma_id FROM some_lookup WHERE program_id = :p AND :i BETWEEN index_start and index_end; -- 4 consistent gets total: -- 1 on the index root block -- 2 on the table to get index_end and ma_id for the index keys (0,2) and (0,10) -- 1 not explained (possibly 1 on the index segment header to get the root block address) exec :p := 0; :i := 991; SELECT ma_id FROM some_lookup WHERE program_id = :p AND :i BETWEEN index_start and index_end; -- 97 consistent gets total: -- 1 on the index root block -- 95 on the table to get index_end and ma_id for the index keys (0,2) .. (0, 990) -- (there are 100 matching keys but some adjacent ones point to the same table block) -- 1 not explained (possibly 1 on the index segment header to get the root block address) set autotrace off -- now repeat uncommenting the creation of unique index iot_like_idx: -- ------------------------------------------------- -- | Id | Operation | Name | Rows | -- ------------------------------------------------- -- | 0 | SELECT STATEMENT | | | -- |* 1 | INDEX RANGE SCAN| IOT_LIKE_IDX | 2 | -- ------------------------------------------------- -- -- 1 - access("PROGRAM_ID"=:P AND "INDEX_END">=:I AND "INDEX_START"<=:I) -- filter("INDEX_START"<=:I) -- note: index-only plan: accesses (walks) the index only (1 block), -- the table is never read since ma_id is already in the index -- -- down to 1 or 2 consistent gets for both queries ... -- Alberto Dell'Era "the more you know, the faster you go"