Forum Stats

  • 3,782,263 Users
  • 2,254,629 Discussions
  • 7,880,032 Comments

Discussions

create index A and A + B and A+B+C

s197oo302
s197oo302 Member Posts: 41
edited Apr 3, 2014 6:44AM in SQL & PL/SQL

This is index question.

If you have oracle database, and have the index A + B + C colume.

You might need the index A+ B or A.

Oracle Database automatically check these case and prevent these case,

So can save disk space and prevent not using original A+ B+ C index.

What is your opinion?

If This is the right scenario we might ask oracle to make constraint on index creation.

So we only have one index A+B+C. and probibit creating A+B or A by mistake.

Give your opinion.

Xie Xie

Answers

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy

    composite index and normal index are good at different times.

    Search on composite index will be slower in compare to if it is done at simple index for that column.

    Composite index may give you faster access if all columns of it are used in predicates in compare to simple index.

    I would suggest you to test it yourself

  • Harmandeep Singh
    Harmandeep Singh Member Posts: 287 Bronze Badge

    As Nimish said, search on composite indexes  is slower. Try yo make index(with minimal columns) only when it is really giving benefits. In case you are suggesting, index on 2 columns might be best as it solves 90% of scenarios.

    Index should only be taken as performance tool .

    Thanks,

    Harman

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Apr 3, 2014 6:29AM

    I can't create database triggers to be fired at index creation, but given the create index statement I could check

    with /* simulating user_ind_columns */

    user_ind_cols(index_name,table_name,column_name,column_position,column_length,char_length,descend) as

    (select 'index_name1','table_name','column_name_1',1,10,10,'ASC' from dual union all

    select 'index_name1','table_name','column_name_2',2,10,10,'ASC' from dual union all

    select 'index_name1','table_name','column_name_3',3,10,10,'ASC' from dual union all

    select 'index_name2','table_name','column_name_1',1,10,10,'ASC' from dual union all

    select 'index_name2','table_name','column_name_4',2,10,10,'ASC' from dual

    ),

    create_index as

    (select regexp_substr(create_index,' on (\w+) ',1,1,'i',1) table_name,

            regexp_substr(create_index,'\((.+)\)',1,1,'i',1) index_columns

       from (select 'create index index_name on table_name (column_name_1,column_name_2)' create_index

               from dual

            )

    )

    select table_name,

           index_name,

           column_list,

           (select index_columns from create_index) to_be_created

      from (select table_name,

                   index_name,

                   listagg(column_name,',') within group (order by column_position) column_list

              from user_ind_cols

             where table_name = (select table_name

                                   from create_index

                                )

             group by table_name,index_name

           )

    where instr(column_list,(select index_columns from create_index)) = 1

    TABLE_NAMEINDEX_NAMECOLUMN_LISTTO_BE_CREATED
    table_nameindex_name1column_name_1,column_name_2,column_name_3column_name_1,column_name_2

    Regards

    Etbin

  • Moazzam
    Moazzam Member Posts: 1,356

    Suppose we have a scenario where most of SQL Statements uses all three A,B,C in their WHERE condition and few queries use only one of above three columns in the WHERE clause. We have two options:


    1. create 4 indexes
      • index for A only
      • index for B only
      • index for C only
      • composite index on A,B.C
    2. Create single composite index on A,B,c


    If we use first option, the select queries shall be very fast whether they use single column (A,B,C) in the where clause or all three columns. We'll be getting best performance assuming that optimizer is using these indexes. But the insert/update/delete statements shall be slow because for every DML operation, Oracle 'll have to update all 4 indexes.


    In case of second option, most of queries using all three columns in where clause shall be fast. However those queries shall be comparatively slow which are using only one of the above three columns due to index range/skip scans. In this case, DML statements shall be fast as now only one index need to be maintained in case of such operation.


    By not restricting us on index creation, Oracle has given us option to decide the number and order of indexes. Different applications have different requirements. Some want SELECT queries fast to display results immediately to the client while some wants fast DML operations.


This discussion has been closed.