performance of Indexes in Composite key

[Sameer]

1. It will/will not use a composite index for two first queries though the columns mentioned in the WHERE clause aren't as specified in the index defination (sequence of column specification). BUT It will definately improve the speed of retrival of data if you specify the sequence of the columns in the WHERE condition as you specified in the index defination. As it is always recommended

Look at the follwing example.

SQL> CREATE TABLE test AS SELECT object_id, object_name, object_type FROM all_objects;

Table created.

SQL> CREATE INDEX ix_test ON test (object_id, object_type);

Index created.

SQL> SELECT object_name FROM test WHERE object_id = 1199  AND object_type = 'VIEW';


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)

SQL> SELECT object_name FROM test
  2    WHERE object_type = 'VIEW' AND  object_id = 1199;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)


-- only second column has been specified in the WHERE clause
so it doesn't use the index.

SQL> SELECT object_name FROM test
  2  WHERE object_type = 'VIEW' ;

1155 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'


SQL> SELECT object_name FROM test WHERE object_id = 1199;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IX_TEST' (NON-UNIQUE)


-- Here you force optimizer to user index by suppling hint

SQL> SELECT /*+ INDEX (test, ix_test) */ object_name FROM test  WHERE object_type = 'VIEW' ;

1155 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=34 Card=10 Bytes=280
          )

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=34 Card=10 B
          ytes=280)

   2    1     INDEX (FULL SCAN) OF 'IX_TEST' (NON-UNIQUE) (Cost=26 Car
          d=10)