Index skip scan in oracle

With Oracle 9i, CBO is equipped with many more features, one of them is “Index skip scan” .This means even if you have a composite index on more than one column and you use the non-prefix column alone in your SQL, it may still use index .CBO will calculate the cost of using the index and if it is more than that of full table scan, then it may not use index.

The index skip scan was introduced to allow Oracle to “skip” leading-edge column  in a multi-column index. You can force an index skip scan with the /*+ index_ss */ hint. For example, consider the following concatenated index on a super-low cardinality column , following by a very selective column . The cardinality of the leading column has a direct impact on the speed of the index skip scan. 
The regular B-tree index will have only one range scan from top to bottom. But skip scan will do many range scan depends on the cardinatlity of the leading index column.  Since the query lacks the leading column it will rewrite the query into smaller queries and each doing a range scan.


SQL> create table mahi.skiptest(a number,b number,c number);

Table created.

SQL> create index mahi.ix1 on mahi.skiptest (a,b);

Index created.

SQL> declare
  2
  3  begin
  4  for i in 1 .. 100000
  5  loop
  6  insert into skiptest values(mod(i, 5), i, 100);
  7  end loop;
  8  commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname => 'MAHI', tabname => 'skiptest', cascade => true);

PL/SQL procedure successfully completed.

SQL> select count(1),a from skiptest group by a;

  COUNT(1)          A
---------- ----------
     20000          1
     20000          2
     20000          4
     20000          3
     20000          0
Note:- column 'a' having 5 unique values ,

SQL> set autotrace on explain
SQL> select * from skiptest where b=88888;


Execution Plan
----------------------------------------------------------
Plan hash value: 380852608

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    10 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SKIPTEST |     1 |    10 |     7   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IX1      |     1 |       |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Optimizer used skip scan index for the above query. During the skip scan, the composite index is accessed once for each distinct value of the leading column(s). For each distinct value, the index is searched to find the query's target values. When optimizer use the skip scan index, the query(select * from skiptest where b=88888;) is broken down into two small range scan as below.

Select * from skiptest where a=0 and b=88888
Union
Select * from skiptest where a=1 and b=88888
Union
Select * from skiptest where a=2 and b=88888
Union
Select * from skiptest where a=3 and b=88888
Union
Select * from skiptest where a=4 and b=88888;

Let us consider the below SQL query and it is using regular B-tree index. Since we use the leading index column(a) in the where clause. This query does only one range scan, not like skip scan index.

SQL> Select * from skiptest where A=1  and B=92271;

         A              B             C

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

          1        92271          100

Execution Plan

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

Plan hash value: 3161369405

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |          |     1 |    10 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SKIPTEST |     1 |    10 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX1      |     1 |       |     1   (0)| 00:00:01 |

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