Index skip scan

We are using the B-Tree index and our predicate does not use the leading edge of index, in this case we might have a table T with an index on T(x,y). we query SELECT * from T WHERE x= 8.

The optimizer will allow to use the index since our predicate did not involve the column X. the optimizer would notice that it did not have to go to the table to get either X or Y. they are in the index. So it may very well option for the fast full index scan.

See below link for more information:http://karthikeyanbaskaran.blogspot.com/2010/02/index-is-not-getting-used.html

The is another case whereby the index T(x,y) could be used by the CBO is during an index skip scan. The skip scan works well if and only if the leading edge of the index has very few distinct values and the optimizer understand that. Example, consider an idex on (GENDER, EMPNO) where GENDER has the values M and F.


SQL> create table t
2 as
3 select decode(mod(rownum,2),0,'M','F') as gender,
4 all_objects.*
5 from all_objects;

Table created.

SQL> create index t_idx on t(gender,object_id);

Index created.

SQL> begin
dbms_stats.gather_table_stats('SYSTEM','T');
end;
/

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from t where object_id = 41;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=94)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=1 Bytes= 94)
2 1 INDEX (SKIP SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1 )
The INDEX SKIP scan step tells us that Oracle is going to skip throughout the index, looking for the points where GENDER changes values and read down the tree from there.

Now we increase the count of the distinct value.
SQL> set autotrace off;
SQL> update t set gender = chr(mod(rownum,256));

29119 rows updated.

SQL> begin
2 dbms_stats.gather_table_stats('SYSTEM','T');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from t where object_id = 41;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=41 Card=1 Bytes=94)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=41 Card=1 Bytes=94)