오라클 인덱스 칼럼 정보 조회

오라클 인덱스 칼럼 정보 조회

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

;