Re: explain plan, can you explain this?

> Alberto's reponse
> (http://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 |
----------------------------------------------------------------
--

• 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"

--
http://www.freelists.org/webpage/oracle-l