CODEWITHSUNDEEP
sqloracle-databaseindexing
SYMA
SYMA

Reputation: 119

How to recreate composite index with the help 'all_ind_columns'

I have a table(XXX) which has composite indexing on two of the columns(A,B). I am extracting those indexes into another table by using query:

create table YYY as
    select index_name, table_name, column_name
    from all_ind_columns
    where table_name = 'XXX';

I get two rows for table XXX, each row representing different columns(A,B) but same index_name(INDEX1).

How shall I recreate them in the same manner.

Upvotes: 1

Views: 161

Answers (1)

schurik
schurik

Reputation: 7928

you can use the following query to generate the "create index" statement:

 select 
   'create index '
 || index_name 
 ||'  on YYY (' 
 || listagg( column_name, ',') within group (order by column_position) 
 || ');'  
from all_ind_columns where table_name='XXX' group by index_name;

Upvotes: 3

Related Questions