오라클 인덱스 칼럼 정보 조회
select index_name
,index_type
,decode(constraint_name, null, ' ', 'pk') primary_key
,uniqueness
,nvl(max(a),' ') index_pos1
,nvl(max(b),' ') index_pos2
,nvl(max(c),' ') index_pos3
,nvl(max(d),' ') index_pos4
,nvl(max(e),' ') index_pos5
,nvl(max(f),' ') index_pos6
,nvl(max(g),' ') index_pos7
,nvl(max(h),' ') index_pos8
,nvl(max(i),' ') index_pos9
,nvl(max(j),' ') index_pos10
from
(
select a.index_name
,a.index_type
,a.uniqueness
,c.constraint_name
,DECODE(column_position, 1, column_name, '') a
,DECODE(column_position, 2, column_name, '') b
,DECODE(column_position, 3, column_name, '') c
,DECODE(column_position, 4, column_name, '') d
,DECODE(column_position, 5, column_name, '') e
,DECODE(column_position, 6, column_name, '') f
,DECODE(column_position, 7, column_name, '') g
,DECODE(column_position, 8, column_name, '') h
,DECODE(column_position, 9, column_name, '') i
,DECODE(column_position, 10, column_name, '') j
from all_indexes a, all_ind_columns b, all_constraints c
where a.table_name = upper('테이블이름')
and a.table_name = b.table_name
and a.index_name = b.index_name
and a.table_name = c.table_name(+)
and a.index_name = c.constraint_name(+)
and c.constraint_type(+) = 'P'
order by index_name, column_position
)
group by index_name, index_type,constraint_name, uniqueness
order by primary_key desc, index_name
;
