首页 > 代码库 > 检查外键列是否是索引列的两个语句
检查外键列是否是索引列的两个语句
本文摘自《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);
执行第一个脚本,输出如下:
执行第二个脚本,输出如下:
检查外键列是否是索引列的两个语句
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。