Index Skip Scan Hint Usage in Oracle Database

Index skip scans improve index scans against non-prefix columns since it is often faster to scan index blocks than scanning table data blocks. A non-prefix index is an index that does not contain a key column as its first column.

This concept is easier to understand if one imagines a prefix index to be similar to a partitioned table. In a partitioned object the partition key (in this case the leading column) defines which partition data is stored within. In the index case every row underneath each key (the prefix column) would be ordered under that key. Thus in a skip scan of a prefixed index, the prefixed value is skipped and the non-prefix columns are accessed as logical sub-indexes. The trailing columns are ordered within the prefix column and so a ’normal’ index access can be done ignoring the prefix.

In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. Hence it is now possible to use the index even if the leading column is not used in a where clause.

Example query and explain plan

Consider the example given below:

drop table at2;
create table at2(a varchar2(3),b varchar2(10),c varchar2(5));

begin
  for i in 1..1000
  loop
  insert into at2 values('M', i, 'M');
  insert into at2 values('F', i, 'F');
  end loop;
  end;
/
create index at2_i on at2(a,b,c);
exec dbms_stats.gather_table_stats(OWNNAME => NULL, TABNAME => 'at2',
       CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1');

set autotrace traceonly
select * from at2 where b='352';

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     2 |    14 |     3   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | AT2_I |     2 |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"='352')
       filter("B"='352')

set autotrace off