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

s197oo302

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

Nimish Garg

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

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

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_NAME	INDEX_NAME	COLUMN_LIST	TO_BE_CREATED
table_name	index_name1	column_name_1,column_name_2,column_name_3	column_name_1,column_name_2

Regards

Etbin

Moazzam

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.