檢查外鍵列是否是索引列的兩個語句,索引語句

來源:互聯網
上載者:User

檢查外鍵列是否是索引列的兩個語句,索引語句

本文摘自《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);


 

執行第一個指令碼,輸出如下:

 

執行第二個指令碼,輸出如下:

 

 

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.