Index skip scan hint explained

Index skip scan Hint Usage

Index skip scans improve index scans by nonprefix columns since it is often faster to scan index blocks than scanning table data blocks.

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:

SQL> create table t1(a number,b number);

Table created.

SQL> begin 

  2  for i in 1..1000

  3  loop

  4  insert into t1 values(i, 56);

  5  end loop;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> create index t1_i on t1(a,b);

Index created.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> explain plan for select /*+ index_ss(t1 t1_i) */ * from t1 where b=56;

Explained.

SQL> start ?\rdbms\admin\utlxpls.sql

-----------------------------------------------------------------

| Operation  |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |

-----------------------------------------------------------------

| SELECT STATEMENT      |     1K|    4K|   1001 |       |       |

|   INDEX SKIP SCAN T1_I|     1K|    4K|   1001 |       |       |-----------------------------------------------------------------