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