Oracle 인덱스 정리 대상 추출 스크립트

Oracle 인덱스 정리 대상 추출 스크립트

---

set linesize 130

set pagesize 10000

col "OWNER" form a8

col table_name form a30

col "삭제대상 INDEX" form a35

col "삭제원인 INDEX" form a51

select

di.table_owner "OWNER"

,di.table_name

,dic1.index_name || chr(10) || '>' || replace(dic1.index_cols,' ',',') || decode(sign(dic1.cnt-6),1,'...') "삭제대상 INDEX"

,dic2.index_name || chr(10) || '>' || replace(dic2.index_cols,' ',',') || decode(sign(dic1.cnt-7),1,'...') "삭제원인 INDEX"

from

dba_indexes di

,(

select

table_owner,table_name,index_owner,index_name,

TRIM(MAX(DECODE(column_position, 1, column_name)) || ' ' ||

MAX(DECODE(column_position, 2, column_name)) || ' ' ||

MAX(DECODE(column_position, 3, column_name)) || ' ' ||

MAX(DECODE(column_position, 4, column_name)) || ' ' ||

MAX(DECODE(column_position, 5, column_name)) || ' ' ||

MAX(DECODE(column_position, 6, column_name))) INDEX_COLS,count(*) cnt

from dba_ind_columns dic

where table_owner not in ('SYS','SYSTEM')

group by

table_owner,table_name,index_owner,index_name

) dic1

,(

select

table_owner,table_name,index_owner,index_name,

TRIM(MAX(DECODE(column_position, 1, column_name)) || ' ' ||

MAX(DECODE(column_position, 2, column_name)) || ' ' ||

MAX(DECODE(column_position, 3, column_name)) || ' ' ||

MAX(DECODE(column_position, 4, column_name)) || ' ' ||

MAX(DECODE(column_position, 5, column_name)) || ' ' ||

MAX(DECODE(column_position, 6, column_name)) || ' ' ||

MAX(DECODE(column_position, 7, column_name))) INDEX_COLS,count(*) cnt

from dba_ind_columns dic

where table_owner not in ('SYS','SYSTEM')

group by

table_owner,table_name,index_owner,index_name

) dic2

where

di.table_owner not in ('SYS','SYSTEM')

and di.owner=dic1.index_owner

and di.index_name=dic1.index_name

and di.table_owner=dic1.table_owner

and di.table_name=dic1.table_name

and dic1.table_owner=dic2.table_owner

and dic1.table_name=dic2.table_name

and dic1.index_name<>dic2.index_name

and dic2.index_cols like dic1.index_cols || '%'

and dic2.cnt>dic1.cnt