Veritabanında overlap(çakışan) indexleri bulmak için gerekli script.

SELECT miv1.table_owner,
       miv1.table_name,
       miv1.index_name,
       miv2.index_name,
       miv1.index_columns,
       miv2.index_columns
  FROM (  SELECT table_owner, table_name, index_name,
                 listagg (c.column_name, ',') WITHIN GROUP (ORDER BY c.column_position) index_columns
            FROM dba_ind_columns c
WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'XDB', 'OLAPSYS')
        GROUP BY table_owner, table_name, index_name) miv1,
       (  SELECT table_owner, table_name, index_name,
                 listagg (c.column_name, ',') WITHIN GROUP (ORDER BY c.column_position) index_columns
            FROM dba_ind_columns c
WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'XDB', 'OLAPSYS')
        GROUP BY table_owner, table_name, index_name) miv2
 WHERE     miv1.table_owner = miv2.table_owner
       AND miv1.table_name = miv2.table_name
       AND miv1.index_columns LIKE miv2.index_columns || '_%';

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

Loading