TIP 58# : Column ordering in composite index

There are a myth about composite index which I `d like to address this here.

1. Whether SQL with condition on leading indexed column can only take advantage of composite index ?

Answer is no. SQL statement which has condition on columns which are not leading column in composite index may take advantage of using script.

Here is sample :

-- Create sample table

create table test as select * from user_objects;

-- Create composite index on tempory and object_id
create index test_idx on test(temporary,object_id);

-- Analyze new table with its index
exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS');

-- Check simple query which has condition on both indexed columns.

As I think, optimizer should use existing index.


SQL> select object_id, object_name from test where temporary='Y' and object_id=19;

no rows selected



Index has been used.








-- Check simple query which has condition on second column .

SQL> select object_id, object_name from test where object_id=245630;

no rows selected


SQL> select object_id, object_name from test where object_id=245630;

no rows selected



Index has been used.








Interestingly, query uses index which its leading indexed column is not object_id
That is the beauty of INDEX SKIP SCAN

More testing ..... Creating composite index with same columns but with reverse order. (This time on object_id and then temporary).


-- Create composite index on object_id and temporary

create index test_idx on test(object_id,temporary);

-- Gather stats

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS');

-- Check simple query which has condition on both indexed columns.

SQL> select object_id, object_name from test where temporary='Y' and object_id=19;

no rows selected




Index has been used.








-- Check simple query which has condition on second column .


select object_id, object_name from test where TEMPORARY='Y';



Index has not been used.









optimizer does not choose composite index. It goes with full table scan

Conclusion :
---------------


In creating of composite index, order of columns is important.
If you put less selective columns as leading columns in index and put most selective columns at the end, other queries which have condition on non-leading index columns may also take advantage of INDEX SKIP SCAN.



For those who are interested, Send me Email for more test cases.