检查外键列是否是索引列的两个语句

最新推荐文章于 2021-04-09 03:05:09 发布

原创最新推荐文章于 2021-04-09 03:05:09 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#foreign key #indexed column

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文介绍了如何通过SQL查询语句检查Oracle数据库中外键列是否为索引列，包括基本查询方法和利用新特性Listagg分析函数的高级查询方法。通过实验证明了查询的有效性和局限性，并提供了测试表创建示例。

本文摘自《oracle 索引技术》第37页和第38页

检查外键列是否是索引列的语句之一：

select distinct
a.owner  owner,
a.constraint_name cons_name,
a.table_name tab_name,
b.column_name  cons_column,
nvl(c.column_name,'***Check index***') ind_column
from dba_constraints a,
     dba_cons_columns b,
     dba_ind_columns c
where constraint_type='R'
and   a.owner=upper('&user_name')
and   a.owner=b.owner
and   a.constraint_name=b.constraint_name
and   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'
  end
else
  '** Check idx **'
end checker,
ind.index_type,cons.owner,cons.table_name, ind.index_name,cons.constraint_name,cons.cols
from 
(
select c.owner,c.table_name,c.constraint_name,listagg(cc.column_name,',') within group (order by cc.column_name) cols
from dba_constraints c, dba_cons_columns cc
where c.owner=cc.owner
and c.owner=upper('&schema')
and c.constraint_name=cc.constraint_name
and c.constraint_type='R'
group by c.owner,c.table_name,c.constraint_name
) cons
left 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 ) ind
on cons.cols=ind.cols
and cons.table_name=ind.table_name
and cons.owner=ind.table_owner
order 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);

执行第一个脚本，输出如下：