檢查外鍵列是否是索引列的兩個語句,索引語句
本文摘自《oracle 索引技術》 第37頁和第38頁
檢查外鍵列是否是索引列的語句之一:
select distincta.owner owner,a.constraint_name cons_name,a.table_name tab_name,b.column_name cons_column,nvl(c.column_name,'***Check index***') ind_columnfrom dba_constraints a, dba_cons_columns b, dba_ind_columns cwhere constraint_type='R'and a.owner=upper('&user_name')and a.owner=b.ownerand a.constraint_name=b.constraint_nameand b.column_name=c.column_name(+)and b.table_name=c.table_name(+)and b.position=c.column_position(+)order by tab_name, ind_column;
以上查詢語句並不能在所有情況下都正確的報告出未索引的外鍵。
比如,在多列外鍵的情況下,以不同於索引列的書序定義約束也不要緊,只要索引列在該索引中位於前面即可。
檢查外鍵列是否是索引列的語句之二:--使用listagg分析函數(該函數是11g新特性)
select case when ind.index_name is not null then case when ind.index_type in ('BITMAP') then '** Bitmp idx **' else 'indexed' endelse '** Check idx **'end checker,ind.index_type,cons.owner,cons.table_name, ind.index_name,cons.constraint_name,cons.colsfrom (select c.owner,c.table_name,c.constraint_name,listagg(cc.column_name,',') within group (order by cc.column_name) colsfrom dba_constraints c, dba_cons_columns ccwhere c.owner=cc.ownerand c.owner=upper('&schema')and c.constraint_name=cc.constraint_nameand c.constraint_type='R'group by c.owner,c.table_name,c.constraint_name) consleft outer join(select table_owner,table_name,index_name,index_type,cbr,listagg(column_name,',') within group (order by column_name) cols from (select ic.table_owner,ic.table_name,ic.index_name,ic.column_name, ic.column_position,i.index_type,connect_by_root(ic.column_name) cbr from dba_ind_columns ic, dba_indexes i where ic.table_owner=upper('&schema') and ic.table_owner=i.table_owner and ic.table_name=i.table_name and ic.index_name=i.index_name connect by prior ic.column_position-1=ic.column_position and prior ic.index_name=ic.index_name ) group by table_owner,table_name,index_name,index_type,cbr ) indon cons.cols=ind.colsand cons.table_name=ind.table_nameand cons.owner=ind.table_ownerorder by checker,cons.owner,cons.table_name;
建立如下的測試表:
create table TEST_USER.students(stud_id number constraint pk_stud_id primary key,lname varchar2(40),fname varchar2(40)); create table TEST_USER.attendees (stud_id number, class_id number, constraint pk_attendees primary key (stud_id,class_id) ); alter table TEST_USER.attendees add constraint fk_stud foreign key (stud_id) references TEST_USER.students(stud_id);
執行第一個指令碼,輸出如下:
執行第二個指令碼,輸出如下: