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

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

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

Regards

Etbin