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