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>