1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Composite Index Performance

Discussion in 'Server Administration and Options' started by Arju, Oct 10, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Composite index is a good way to improve performance of the oracle database. But before creating it think about SQL queries that run against it. The order of the columns in the composite index is really important. The rule is make the most selective column first of the order in composite index.

    Suppose if you create composite index on the column a , b and c in order of (a,b,c) then if in the query inside where clause or join
    -a, ab, and abc combinations of columns will use the index.
    -bc, b, and c combinations of columns will not use the composite index.

    Below is the details with example.
    Code (Text):

    SQL> set autot trace
    SQL> set timi on
    SQL> set lines 150
    SQL> create table test_composite_I as select object_name,object_id,data_object_id from dba_objects;

    Table created.

    Elapsed: 00:00:01.34
    SQL> drop table   test_composite_I ;

    Table dropped.

    Elapsed: 00:00:01.81
    SQL> create table composite_index_test as select owner,object_name,object_id,data_object_id from dba_objects;

    Table created.
    Elapsed: 00:00:00.50

    SQL> create index composite_index_test_I on composite_index_test(object_name,object_id,data_object_id);

    Index created.

    Elapsed: 00:00:00.57
    SQL> exec dbms_stats.gather_table_stats('SYS','COMPOSITE_INDEX_TEST');

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:03.85
     
    As our order of creating index is object_name,object_id,data_object_id so where clause with only object_id, or/and data_object_id will not use index.
    Code (Text):

    SQL> select * from composite_index_test where object_id=1;

    no rows selected

    Elapsed: 00:00:00.14

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1100627003

    ------------------------------------------------------------------------------------------
    | Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |                      |     1 |    35 |    65   (4)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| COMPOSITE_INDEX_TEST |     1 |    35 |    65   (4)| 00:00:01 |
    ------------------------------------------------------------------------------------------
     
    As our composite index first column is object_name so usage of object_name in the where clause will use index.
    Code (Text):

    SQL> select * from composite_index_test where object_name='TEST';

    Elapsed: 00:00:00.03

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3896276822

    ------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                        |     2 |    70 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| COMPOSITE_INDEX_TEST   |     2 |    70 |     3   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | COMPOSITE_INDEX_TEST_I |     2 |       |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------
    SQL> select * from composite_index_test where object_name='TEST' and data_object_id=10;

    no rows selected

    Elapsed: 00:00:00.01

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3896276822

    ------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                        |     1 |    35 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| COMPOSITE_INDEX_TEST   |     1 |    35 |     3   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | COMPOSITE_INDEX_TEST_I |     1 |       |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------