抓出外键没创建索引的表

此脚本不依赖统计信息。

建议在外键列上创建索引，外键列不创建索引容易导致死锁。级联删除的时候，外键列没有索引会导致表被全表扫描。以下脚本抓出 Scott 账户下外键没创建索引的表。

with cons as (select /*+ materialize */ owner, table_name, constraint_name
          from dba_constraints
         where owner = 'SCOTT'
           AND constraint_type = 'R'),
     idx as (
     select /*+ materialize */ table_owner,table_name, column_name
          from dba_ind_columns
         where table_owner = 'SCOTT')      
select owner,table_name,constraint_name,column_name
  from dba_cons_columns
 where (owner,table_name, constraint_name) in
       (select * from cons)
   and (owner,table_name, column_name) not in
       (select * from idx);

在 Scott 账户中，EMP 表的 deptno 列引用了 DEPT 表的 deptno 列，但是没有创建索引，因此我们通过脚本可以将其抓出。

 with cons as (select /*+ materialize */ owner, table_name, constraint_name
           from dba_constraints
           where owner = 'SCOTT'
             AND constraint_type = 'R'),
       idx as (
       select /*+ materialize */ table_owner,table_name, column_name
            from dba_ind_columns
           where table_owner = 'SCOTT')
  select owner,table_name,constraint_name,column_name
     from dba_cons_columns
   where (owner,table_name, constraint_name) in
         (select * from cons)
      and (owner,table_name, column_name) not in
         (select * from idx);

OWNER    TABLE_NAME      CONSTRAINT_NAME      COLUMN_NAME
-------- --------------- -------------------- --------------------
SCOTT    EMP             FK_DEPTNO            DEPTNO