블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30            
  • 35,390total
  • 16today
  • 2yesterday
2018.06.15 13:58 오라클

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

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
;


posted by bedbmsguru