Oracle INDEX SKIP SCAN

Oracle INDEX SKIP SCAN

Oracle 9i Skip Scan
Objectives:
An improvement over index range/full scan when there are non-equality start/stop
keys.
Example(to be walked through):
create index si on emp(deptno, sal);
select count(*) from emp
where deptno > 10 and sal = 35000;
Oracle 9i Skip Scan
Functionality:
Optimizer hints: index_ss(), index_ss_desc() and index_ss_asc().
Index types not supported: single column indexes, reverse indexes,
functional indexes or bitmap indexes.
Predicates not supported: like.
Oracle 9i Skip Scan
Optimizer(ask Hakan Jakobsson for details):
1. kkofmx(): mark index and/or skip- scan -only.
2. kkonxc(): exclude index types and predicates not supported.
3. kkessc(): cost skip and range scan and choose one accordingly.
4. skip scan cost: (est. no. of distinct keys:) * (no. of leaf blocks) /

(no. of distinct keys in the index)
Oracle 9i Skip Scan
QKA/Row Source/PQ(ask Chi Ku for details):
1. FROKISSA/FROKISSD: ascending/descending skip scan
access path.
2. RWSTSSL/RWSTSSP: IOT/regular index skip scan row sources.
3. kkfdtbl(): call kkfdtip() to allocate an index skip scan DN.
Oracle 9i Skip Scan
Index layer(ask Dmitry Potapov for details: kdiss.c kdiss.h)
kdiss/KDITSK: index skip scan state and index skip scan type.
Methods: kdiss_init()/kdiss_fetch()/kdiss_end_ scan ().
Start/Stop keys: skip scan uses column key values to qualify index
rows.
Start/Stop Operators: skip scan uses start/stop operator to qualify
index rows.
Oracle 9i Skip Scan
Index Layer(continued):
kdiss_init(): initialize the skip scan state.
kdiss_fetch(): performs binary search with backtrack starting from
root using start/stop keys and start/stop operators to
qualify index rows. The path from root to the current
leaf block are pinned during the scan .

kdiss_end_ scan (): releases all pins and resources, as well as, nullify
the scan state.
Oracle 9i Skip Scan
Performance:
Skip scan performance is a function of no. of distinct keys.
Experiments on a Sun Ultra 60: 1504 2K leaf blocks
create table test (a number, b number, c number, d number);
create index test_abcd on test(a, b, c, d);
load test with 100000 rows which as 10 distinct values in a,
100 distinct values in b, 1000 disctinct values in c and 10000
distinct values in d.
Oracle 9i Skip Scan
select count(d) from test where b = 10;
skip scan /f.f. scan : 0.1/0.9 second; cons. reads: 50; bin srch: 924
select count(d) from test where c = 10;
skip scan /f.f. scan : 0.09/0.77 second; cons. reads: 135; bin srch: 1984
select count(d) from test where d = 10;
skip scan /f.f. scan : 1.31/0.78 seconds; cons. reads: 1041;
bin srch: 11702