Index Skip Scanning

Index skip scan is one of the new feature in oracle9i. Prior to oracle9i, if we have composit index and if we are not using leading column in the WHERE clause, then optimizer will not use the index. But in oracle9i and further versions, optimizer may use the skip scan index depends on the cardinality of the leading column and optimizer cost. 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.

The advantage of index skip scan is, we can reduce the number of indexes on the table. It save index space and reduce the index maintenance. Skip scan index is not same as efficient as B-tree index. But it is better then the full table scan.

Here is an example.

SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER
SEX VARCHAR2(10)
NAME VARCHAR2(20)

SQL> select count(*),sex from emp group by sex;

COUNT(*) SEX
---------- ----------
10000 F
10000 M

SQL> create index idx_emp on emp(sex,empno);

Index created.
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'MWSADMIN',TABNAME => 'EMP',ESTIMATE
_PERCENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);

PL/SQL procedure successfully completed.
SQL> set autotrace on explain;
SQL> select name from emp where empno=1934;

NAME
--------------------
Scott
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=2 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=4 Card=2 Bytes=20)
2 1 INDEX (SKIP SCAN) OF 'IDX_EMP' (NON-UNIQUE) (Cost=3 Card=2)

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 name from emp where empno=1934) is broken down into two small range scan as below.


select name from emp where sex = 'M' and empno=1934
union
select name from emp where sex = 'F' and empno=1934

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

SQL> select name from emp where sex='M' and empno=1934;

NAME
--------------------
Scott

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=11)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=11)
2 1 INDEX (RANGE SCAN) OF 'IDX_EMP' (NON-UNIQUE) (Cost=1 Card=1)