ORACLE FK 인덱스
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
FK 인덱스[편집]
select 'alter table ' || t1_table_name
|| ' add constraint ' || t1_constraint_name
|| ' foreign key (' || t1_column_names || ')'
|| ' references ' || t2_table_name
|| '(' || t2_column_names || ');' FK_script
from
(select a.table_name t1_table_name
, a.constraint_name t1_constraint_name
, b.r_constraint_name t2_constraint_name
-- Concatenate columns to handle composite
-- foreign keys
, listagg(a.column_name,', ')
within group (order by a.position)
as t1_column_names
from user_cons_columns a
, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name
, a.constraint_name
, b.r_constraint_name
) t1,
(select a.constraint_name t2_constraint_name
, a.table_name t2_table_name
-- Concatenate columns for PK/UK referenced
-- from a composite foreign key
, listagg(a.column_name,', ')
within group (order by a.position)
as t2_column_names
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name
, a.constraint_name ) t2
where t1.t2_constraint_name = t2.t2_constraint_name
-- and t2.t2_table_name in ('TB_PG_POG_GDS_DTLS_H')