Index Skip Scan

If you have an composite index  (Index consist of more  than one  column) and you use the non-prefix column alone in your SQL, it may still use index  dependening  of the  cost  (CBO will calculate the cost of using the index and compare it with full table scan)

Index skip scan works differently from a normal index (range) scan.A normal range scan works from top to bottom first and then move horizontal.But a Skip scan includes several range scans in it. Since the query lacks the leading column it will rewrite the query into smaller queries and each doing a range scan

Example

SQL>create table SERDAR.skiptest(a number,b number,c number);
SQL>create index SERDAR.ix1 on SERDAR.skiptest (a,b);
SQL>
declare

begin
for i in 1 .. 100000
loop
  insert into skiptest values(mod(i, 5), i, 100);
end loop;
commit;
end;
/

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

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

Execution Plan


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

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22 Card=1 Bytes=10)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SKIPTEST' (TABLE) (Cost=22 Card=1 Bytes=10)
2 1 INDEX (SKIP SCAN) OF 'IX1' (INDEX) (Cost=21 Card=1)


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.Above example, “select * from test where b=88888” was broken down to several small range scan queries. It was effectively equivalent to following

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;

Conclusion 
Skip scan is not as efficient as normal “single range scan”