Index Skip Scan

Oracle9i New Feature Series: Index Skip Scan

In pre-Oracle9i releases a composite index could only be used if the leading edge column of the index was referenced in the WHERE clause of a statement. In Oracle9i this restriction is removed because the optimizer can perform skip scans to retrieve rowids for values that do not use the prefix. 

Skip scanning lets a composite index be split logically into smaller sub-indexes. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the non-leading key of the index.

The index and table must be analyzed for Oracle to take advantage of index skip scan. 

SQL>
SQL> create table test as
  2  select * from dba_objects;
 
Table created.
 
SQL> create index test_i1 on test (owner, object_name);
 
Index created.
 
SQL> set autotrace on
SQL> select object_type, object_name
  2  from test
  3  where object_name = 'DWA_JOB_LOG';
 
OBJECT_TYPE        OBJECT_NAME
------------------ -------------------------
TABLE              DW_JOB_LOG
 
1 row selected.
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'
 
SQL>
SQL> analyze table test compute statistics;
 
Table analyzed.
 
SQL> select object_type, object_name
  2  from test
  3* where object_name = 'DWA_JOB_LOG'
SQL> /
 
OBJECT_TYPE        OBJECT_NAME
------------------ -------------------------
TABLE              DW_JOB_LOG
 
1 row selected.
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=2 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=46 Card=2 By tes=62)
   2    1     INDEX (SKIP SCAN) OF 'TEST_I1' (NON-UNIQUE) (Cost=45 Card=2)
 
SQL>

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.