Thursday, January 15, 2009

INDEX SKIP SCAN

A new feature called INDEX SKIP SCAN was introduced in Oracle 9i. By which oracle has the ability to skip some column of the index and go with the rest.

For example if you have a index with column (NAME, EMPLID), before 9i when you specify a where condition which just have EMPLID then the index is not considered.

But from 9i the new feature enables the optimizer to skip the NAME field of the index a go for the EMPLID.

A simple test case can explain this. See below.

SQL> create table t
2 as
3 select 'MY_TEST_DATA' obj_desc, object_id, object_name, object_type
4 from all_objects
5 /

Table created.

SQL> create index t_idx on t(obj_desc, object_id)
2 /

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade=>true)

PL/SQL procedure successfully completed.

SQL> select * from t where object_id = 40
2 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=45)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=4 Card=1 Bytes=45)
2 1 INDEX (SKIP SCAN) OF 'T_IDX' (INDEX) (Cost=3 Card=1)


SQL> select * from t where obj_desc = 'MY_TEST_DATA' and object_id = 40
2 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=45)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=4 Card=1 Bytes=45)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=3 Card=1)


But i am not sure if SKIP SCAN is as good as RANGE SCAN. But you can be certain that its better than FULL SCAN.

But again the selection of this option by the optimizer depends on the cardinality of the first field. In the above example we saw that OBJ_DESC had one value and hence the optimizer went for a SKIP SCAN.

Lets take a case where the first column of the index has a high cardinality. Here i am going to create OBJ_DESC field with unique values. lets see what happens.

SQL> drop table t
2 /

Table dropped.

SQL> create table t
2 as
3 select 'MY_TEST_DATA'||rownum obj_desc, object_id, object_name, object_type
4 from all_objects
5 /

Table created.

SQL> create index t_idx on t(obj_desc, object_id)
2 /

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade=>true)

PL/SQL procedure successfully completed.

SQL> select * from t where object_id = 40
2 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=209 Card=1 Bytes=50)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=209 Card=1 Bytes=50)


SQL> select * from t where obj_desc = 'MY_TEST_DATA' and object_id = 40
2 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=50)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=4 Card=1 Bytes=50)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=3 Card=1)




Now you can very well see that the Optimizer decides to go for a FULL SCAN.