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 |
----------------------------------------------------------------
--
--   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"
--
http://www.freelists.org/webpage/oracle-l

Other related posts: