Composite Index - When It Will Be Used?
An index that is created on multiple columns of a table is called composite index.
If you create a composite index on three columns for example a,b,c of a table
composite index will be used for abc, ab, a combination of columns in where clause .
Composite index will not be used for bc,b and c combinations of columns in where clause.
Here is the example
Code:
sql> create table test_data ( a number, b number, c number);
table created.
--- to insert 500 records
sql> declare
2 i number;
3 begin
4 for i in 1..500
5 loop
6 insert into test_data values(i,i,i);
7 end loop;
8 commit;
9 end;
10 /
pl/sql procedure successfully completed.
Sql> create index test_data_idx on test_data(a,b,c);
index created.
Ex1:- ab combination will use index
Code:
sql> set autotrace on explain;
sql> select * from test_data
2 where a = 100
3 and b = 100;
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 index (range scan) of 'test_data_idx' (non-unique)
Ex2:- bc combination in where clause will not use index
Code:
sql> select * from test_data
2 where b = 100
3 and c = 100;
execution plan
----------------------------------------------------------
0 select statement optimizer=choose
1 0 table access (full) of 'test_data'