当前位置：主页 > 查看内容

Oracle中关于外键缺少索引的探讨和总结

发布时间：2021-04-14 00:00| 有位朋友查看

简介：本文转载自微信公众号「DBA闲思杂想录」，作者潇湘隐者。转载本文请联系DBA闲思杂想录公众号。在ORACLE数据库中，定义外键约束时，ORACLE是不会自动创建对应索引的，必须手动在外键约束相关的列上创建索引。那么外键字段上是否有必要创建索引呢?如果有必要……

本文转载自微信公众号「DBA闲思杂想录」，作者潇湘隐者。转载本文请联系DBA闲思杂想录公众号。

在ORACLE数据库中，定义外键约束时，ORACLE是不会自动创建对应索引的，必须手动在外键约束相关的列上创建索引。那么外键字段上是否有必要创建索引呢?如果有必要的话，巡检时，如何找出外键字段上没有创建索引的相关表，并生成对应的索引的脚本呢?

外键缺失索引的影响

外键列上缺少索引会带来三个问题，限制并发性、影响性能、还有可能造成死锁。所以对于绝大部分场景，我们应该尽量考虑在外键上面创建索引

影响性能。如果子表外键没有创建索引，那么当父表查询关联子表时，子表将进行全表扫描。影响表连接方式。
影响并发。无论是更新父表主键，或者删除一个父记录，都会在子表中加一个表锁(在这条语句完成前，不允许对子表做任何修改)。这就会不必要地锁定更多的行，而影响并发性
在特殊情况下，还有可能造成死锁。

我们先来看看一个简单的例子，看看当外键缺失索引时，子表是否进行全表扫描，如下所示，表EMP与DEPT存在主外键关系：

SQL> set autotrace on; 
SQL>  
SQL> SELECT  D.DEPTNO, COUNT(*) 
  2  FROM SCOTT.EMP E INNER JOIN SCOTT.DEPT D ON E.DEPTNO =D.DEPTNO 
  3  GROUP BY D.DEPTNO; 
  
    DEPTNO   COUNT(*) 
---------- ---------- 
        30          6 
        20          5 
        10          3 
  
  
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 4067220884 
  
--------------------------------------------------------------------------- 
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
--------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT   |      |     3 |     9 |     4  (25)| 00:00:01 | 
|   1 |  HASH GROUP BY     |      |     3 |     9 |     4  (25)| 00:00:01 | 
|*  2 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 | 
--------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter("E"."DEPTNO" IS NOT NULL) 
  
  
Statistics 
---------------------------------------------------------- 
          1  recursive calls 
          0  db block gets 
          7  consistent gets 
          0  physical reads 
          0  redo size 
        665  bytes sent via SQL*Net to client 
        524  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
          3  rows processed

如上所示，当外键字段没有索引时，父表与子表关联时，子表会进行全表扫描，下面，我在外键字段创建索引后，就能避免子表表扫描了。

CREATE INDEX SCOTT.IX_DEPTNO ON SCOTT.EMP ("DEPTNO") TABLESPACE USERS;

当然这两个表的数据量实在是太少了，性能上差别不大，当数据量增长上去后，这个性能差异就会比较明显了。如下例子所示，我们构造一个数据量相对较大的父表与子表的案例：

create table parent_tb_test  
( 
    id         number(10), 
    name       varchar2(32), 
    constraint pk_parent_tb_test primary key(id) 
); 
  
create table child_tb_test 
( 
     c_id        number(10), 
     f_id        number(10), 
     child_name  varchar2(32), 
     constraint  pk_child_tb_test primary key(c_id), 
     foreign     key(f_id) references parent_tb_test 
); 
  
  
begin 
     
for index_num in 1 .. 10000 loop 
    insert into parent_tb_test 
    select index_num , 'kerry' || to_char(index_num) from dual; 
     
    if mod(index_num,100) = 0 then 
        commit; 
    end if; 
end loop; 
  
     commit; 
     
end; 
/ 
  
declare index_num number :=1; 
begin 
  
    for index_parent  in 1 .. 10000 loop 
        for index_child in 1 .. 1000 loop 
             insert into child_tb_test 
             select index_num, index_parent, 'child' || to_char(index_child) from dual; 
              
             index_num := index_num +1; 
             if mod(index_child,1000) = 0 then 
                 commit; 
             end if; 
        end loop; 
    end loop; 
  
    commit; 
end; 
/ 
  
  
SQL> execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'PARENT_TB_TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); 
  
PL/SQL procedure successfully completed. 
  
SQL> execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'CHILD_TB_TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO'); 
  
PL/SQL procedure successfully completed. 
  
SQL>

上面脚本构造了测试用的例子和数据，那么我们对比看看外键有无索引的区别：

SQL> set linesize 1200 
SQL> set autotrace traceonly 
SQL> select p.id , p.name,c.child_name 
  2  from test.parent_tb_test p 
  3  inner join test.child_tb_test c on p.id = c.f_id  
  4  where p.id=1000; 
  
1000 rows selected. 
  
  
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 901213199 
  
-------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |                   |  1009 | 44396 |  4706  (21)| 00:00:07 | 
|   1 |  NESTED LOOPS                |                   |  1009 | 44396 |  4706  (21)| 00:00:07 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| PARENT_TB_TEST    |     1 |    31 |     1   (0)| 00:00:01 | 
|*  3 |    INDEX UNIQUE SCAN         | PK_PARENT_TB_TEST |     1 |       |     1   (0)| 00:00:01 | 
|*  4 |   TABLE ACCESS FULL          | CHILD_TB_TEST     |  1009 | 13117 |  4705  (21)| 00:00:07 | 
-------------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   3 - access("P"."ID"=1000) 
   4 - filter("C"."F_ID"=1000) 
  
  
Statistics 
---------------------------------------------------------- 
          0  recursive calls 
          0  db block gets 
      32855  consistent gets 
      32772  physical reads 
          0  redo size 
      29668  bytes sent via SQL*Net to client 
       1218  bytes received via SQL*Net from client 
         68  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
       1000  rows processed 
  
SQL>

创建索引后，我们再来看看其执行计划，注意对比创建索引前后，执行计划的差异，如下所示：

SQL> create index ix_child_tb_test on child_tb_test(f_id); 
  
SQL> set linesize 1200 
SQL> set autotrace traceonly 
SQL> select p.id , p.name,c.child_name 
  2  from test.parent_tb_test p 
  3  inner join test.child_tb_test c on p.id = c.f_id  
  4  where p.id=1000;

接下来，我们再来看看外键缺失索引影响并发，以及造成死锁的情况，如下所示，创建表dead_lock_parent与dead_lock_foreign，两者存在主外键关系，分布插入两条测试数据：

SQL> create table dead_lock_parent( id number primary key, name varchar2(32)); 
  
Table created. 
  
SQL> create table dead_lock_foreign(fid  number, fname varchar2(32), foreign key(fid) references dead_lock_parent); 
  
Table created. 
  
SQL> insert into dead_lock_parent values( 1, 'kerry'); 
  
1 row created. 
  
SQL> insert into dead_lock_foreign values(1, 'kerry_fk');   
  
1 row created. 
  
SQL> insert into dead_lock_parent values(2, 'jimmy'); 
  
1 row created. 
  
SQL> insert into dead_lock_foreign values(2, 'jimmy_fk'); 
  
1 row created. 
  
SQL> commit; 
  
Commit complete. 
  
SQL>

1：在会话1(会话ID为789)里面执行下面SQL语句：

SQL> show user; 
USER 为 "TEST" 
SQL> select * from v$mystat where rownum=1; 
  
       SID STATISTIC#      VALUE 
---------- ---------- ---------- 
       789          0          1 
  
SQL> delete from dead_lock_foreign where fid=1; 
  
已删除 1 行。

2：在会话2(会话ID为766)里面执行下面SQL语句：

SQL> show user; 
USER is "TEST" 
SQL> select * from v$mystat where rownum=1; 
  
       SID STATISTIC#      VALUE 
---------- ---------- ---------- 
       766          0          1 
  
SQL> delete from dead_lock_foreign where fid=2; 
  
1 row deleted.

3：接着在会话1(会话ID为789)里执行删除dead_lock_parent中id为1的记录：

SQL> delete from dead_lock_parent where id=1;

此时你会发现会话被阻塞了，我们可以用下面SQL查询具体的阻塞信息。

COL MODE_HELD FOR A14; 
COL LOCK_TYPE FOR A8; 
COL MODE_REQUESTED FOR A10; 
COL OBJECT_TYPE FOR A14; 
COL OBJECT_NAME FOR A20; 
SELECT LK.SID, 
       DECODE(LK.TYPE, 
              'TX', 
              'Transaction', 
              'TM', 
              'DML', 
              'UL', 
              'PL/SQL User Lock', 
              LK.TYPE) LOCK_TYPE, 
       DECODE(LK.LMODE, 
              0, 
              'None', 
              1, 
              'Null', 
              2, 
              'Row-S (SS)', 
              3, 
              'Row-X (SX)', 
              4, 
              'Share', 
              5, 
              'S/Row-X (SSX)', 
              6, 
              'Exclusive', 
              TO_CHAR(LK.LMODE)) MODE_HELD, 
       DECODE(LK.REQUEST, 
              0, 
              'None', 
              1, 
              'Null', 
              2, 
              'Row-S (SS)', 
              3, 
              'Row-X (SX)', 
              4, 
              'Share', 
              5, 
              'S/Row-X (SSX)', 
              6, 
              'Exclusive', 
              TO_CHAR(LK.REQUEST)) MODE_REQUESTED,  
       OB.OBJECT_TYPE, 
       OB.OBJECT_NAME, 
       LK.BLOCK, 
       SE.LOCKWAIT 
  FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE 
 WHERE LK.TYPE IN ('TM', 'UL') 
   AND LK.SID = SE.SID 
   AND LK.ID1 = OB.OBJECT_ID(+) 
 AND SE.SID IN (766,789) 
 ORDER BY SID;

上面信息如果不能让你理解，那么可以看看下面脚本，相信你能看得更详细。

SQL> SELECT S.SID                             SID,  
            S.USERNAME                        USERNAME,  
            S.MACHINE                         MACHINE,  
            L.TYPE                            TYPE,  
            O.OBJECT_NAME                     OBJECT_NAME,  
         DECODE(L.LMODE, 0, 'None',  
                         1, 'Null',  
                         2, 'Row Share',  
                         3, 'Row Exlusive',  
                         4, 'Share',  
                         5, 'Sh/Row Exlusive',  
                         6, 'Exclusive')   lmode,  
         DECODE(L.REQUEST, 0, 'None',  
                           1, 'Null',  
                           2, 'Row Share',  
                           3, 'Row Exlusive',  
                           4, 'Share',  
                           5, 'Sh/Row Exlusive',  
                           6, 'Exclusive') request,  
         L.BLOCK                           BLOCK  
  FROM   V$LOCK L,  
         V$SESSION S,  
         DBA_OBJECTS O  
  WHERE  L.SID = S.SID  
         AND USERNAME != 'SYSTEM'  
         AND O.OBJECT_ID(+) = L.ID1  
         AND S.SID IN ( 766,789)  
  ORDER  BY S.SID;  
  
       SID USERNAME MACHINE        TY OBJECT_NAME          LMODE           REQUEST         BLOCK 
---------- -------- -------------- -- -------------------- --------------- --------------- ----- 
       766 TEST     XXXX\GET253194 TX                      Exclusive       None                0 
       766 TEST     XXXX\GET253194 TM DEAD_LOCK_FOREIGN    Row Exlusive    None                1 
       766 TEST     XXXX\GET253194 TM DEAD_LOCK_PARENT     Row Exlusive    None                0 
       789 TEST     DB-Server.loca TX                      Exclusive       None                0 
                    ldomain 
  
       789 TEST     DB-Server.loca TM DEAD_LOCK_PARENT     Row Exlusive    None                0 
                    ldomain 
  
       789 TEST     DB-Server.loca TM DEAD_LOCK_FOREIGN    Row Exlusive    Sh/Row Exlusive     0 
                    ldomain

接着在会话2里面执行下面SQL，删除主表中id=2的记录

SQL> delete from dead_lock_parent where id=2;

你会发现会话1就会出现Deadlock

如果你在外键字段上创建索引，那么这种情况下的操作就不会出现死锁。在这里就不再赘述。有兴趣可以测试一下.

外键创建索引建议(Foreign Key Indexing Tips)

虽然增加索引，可能会带来一些额外的性能开销(DML操作开销增加)和磁盘空间方面的开销，但是相比其带来的性能改善而言，这些额外的开销其实完全可以忽略。如果没有其他特殊情况，建议所有的外键字段都加上索引。在Oracle Oracle Database 9i/10g/11g编程艺术这本书中介绍了在什么时候不需要对外键加索引. 必须满足下面三个条件：

不会删除父表中的行。
不论是有意还是无意，总之不会更新父表的唯一/主键字段值。
不会从父表联结到子表，或者更通俗的讲，外键列不支持子表的一个重要访问路径，而且你在谓词中没有使用这些外键累从子表中选择数据。

找出未索引的外键

我们首先可以通过下面脚本，找到整个数据库中那些表有主外键关系，并列出主外键约束.

--查看整个数据库下拥有主外键关系的所有表(排除一些系统用户)

--查看整个数据库下拥有主外键关系的所有表（排除一些系统用户）    
SELECT DC.OWNER                   AS "PARENT_TABLE_OWNER",  
       DC.TABLE_NAME              AS "PARENT_TABLE_NAME",  
       DC.CONSTRAINT_NAME         AS "PRIMARY CONSTRAINT NAME",  
       DF.CONSTRAINT_NAME         AS "REFERENCED CONSTRAINT NAME",  
       DF.OWNER                   AS "CHILD_TABLE_OWNER",  
       DF.TABLE_NAME              AS "CHILD_TABLE_NAME"  
FROM   DBA_CONSTRAINTS DC,  
       (SELECT C.OWNER,  
               C.CONSTRAINT_NAME,  
               C.R_CONSTRAINT_NAME,  
               C.TABLE_NAME  
        FROM   DBA_CONSTRAINTS C  
        WHERE  CONSTRAINT_TYPE = 'R') DF  
WHERE  DC.CONSTRAINT_NAME =DF.R_CONSTRAINT_NAME  
       AND DC.OWNER NOT IN ( 'SYSTEM', 'SYS', 'DBSNMP', 'EXFSYS',  
                            'ORDDATA', 'CTXSYS', 'OLAPSYS', 'MDSYS',  
                            'SYSMAN' );

--查看某个Schema下拥有主外键关系的所有表

--查看某个Schema下拥有主外键关系的所有表    
SELECT DC.OWNER           AS "PARENT_TABLE_OWNER",  
       DC.TABLE_NAME      AS "PARENT_TABLE_NAME",  
       DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",  
       DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",  
       DF.OWNER           AS "CHILD_TABLE_OWNER",  
       DF.TABLE_NAME      AS "CHILD_TABLE_NAME"  
FROM   DBA_CONSTRAINTS DC,  
       (SELECT C.OWNER,  
               C.CONSTRAINT_NAME,  
               C.R_CONSTRAINT_NAME,  
               C.TABLE_NAME  
        FROM   DBA_CONSTRAINTS C  
        WHERE  CONSTRAINT_TYPE = 'R') DF  
WHERE  DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME  
       AND DC.OWNER =UPPER('&OWNER');

--查看某个具体的表是否和其它表拥有主外键关系

--查看某个具体的表是否和其它表拥有主外键关系   
SELECT DC.OWNER           AS "PARENT_TABLE_OWNER",  
       DC.TABLE_NAME      AS "PARENT_TABLE_NAME",  
       DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",  
       DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",  
       DF.OWNER           AS "CHILD_TABLE_OWNER",  
       DF.TABLE_NAME      AS "CHILD_TABLE_NAME"  
FROM   DBA_CONSTRAINTS DC,  
       (SELECT C.OWNER,  
               C.CONSTRAINT_NAME,  
               C.R_CONSTRAINT_NAME,  
               C.TABLE_NAME  
        FROM   DBA_CONSTRAINTS C  
        WHERE  CONSTRAINT_TYPE = 'R') DF  
WHERE  DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME  
       AND DC.OWNER =UPPER('&OWNER') 
       AND DC.TABLE_NAME=UPPER('&TABLE_NAME');

接下来我们要找出在具体的外键字段是否有索引，脚本如下所示：

SELECT   CON.OWNER , 
         CON.TABLE_NAME, 
         CON.CONSTRAINT_NAME, 
         CON.COL_LIST, 
         'No Indexed' AS INDEX_STATUS 
FROM 
     (SELECT CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME, 
           MAX(DECODE(POSITION, 1,     '"' || 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 2,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 3,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 4,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 5,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 6,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 7,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 8,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 9,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 10,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST 
           FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CC 
           WHERE DC.OWNER = CC.OWNER 
           AND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME 
           AND DC.CONSTRAINT_TYPE = 'R' 
           AND DC.OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS', 'ADMIN') 
           GROUP BY CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME  
     ) CON 
      WHERE NOT EXISTS ( 
        SELECT 1 FROM 
                  ( SELECT TABLE_OWNER, TABLE_NAME,    
                       MAX(DECODE(COLUMN_POSITION, 1,     '"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 2,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 3,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 4,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 5,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 6,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 7,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 8,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 9,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 10,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) COL_LIST 
                       FROM DBA_IND_COLUMNS  
                   WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS') 
                   GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COL 
    WHERE CON.OWNER = COL.TABLE_OWNER  
    AND CON.TABLE_NAME = COL.TABLE_NAME   
    AND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) )  ;

如果是ORACLE 11g或以上版本，数据库有分析函数LISTAGG的话，可以使用下面脚本

SELECT CASE  
         WHEN B.TABLE_NAME IS NULL THEN 'NO INDEXED'  
         ELSE 'INDEXED'  
       END               AS STATUS,  
       A.TABLE_OWNER     AS TABLE_OWNER,  
       A.TABLE_NAME      AS TABLE_NAME,  
       A.CONSTRAINT_NAME AS FK_NAME,  
       A.FK_COLUMNS      AS FK_COLUMNS,  
       B.INDEX_NAME      AS INDEX_NAME,  
       B.INDEX_COLUMNS   AS INDEX_COLUMNS  
FROM   (SELECT A.OWNER                              AS TABLE_OWNER,  
               A.TABLE_NAME                         AS TABLE_NAME,  
               A.CONSTRAINT_NAME                    AS CONSTRAINT_NAME,  
               LISTAGG(A.COLUMN_NAME, ',')  
                 WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS  
        FROM   DBA_CONS_COLUMNS A,  
               DBA_CONSTRAINTS B  
        WHERE  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME  
               AND B.CONSTRAINT_TYPE = 'R'  
               AND A.OWNER = B.OWNER  
               AND A.OWNER NOT IN ( 'SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN',  
                                    'MDSYS' )  
        GROUP  BY A.OWNER,  
                  A.TABLE_NAME,  
                  A.CONSTRAINT_NAME) A,  
       (SELECT TABLE_OWNER,  
               TABLE_NAME,  
               INDEX_NAME,  
               LISTAGG(C.COLUMN_NAME, ',')  
                 WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS  
        FROM   DBA_IND_COLUMNS C  
        GROUP  BY TABLE_OWNER,  
                  TABLE_NAME,  
                  INDEX_NAME) B  
WHERE  A.TABLE_NAME = B.TABLE_NAME(+)  
       AND A.TABLE_OWNER = B.TABLE_OWNER(+)  
       AND B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS  
                                   || '%'  
ORDER  BY 1 DESC

自动生成创建外键索引的脚本

上面的这些脚本已经能找出那些外键字段已经建立或未建立索引，此时如果对外键字段缺少索引的表手工创建索引的话，如果数量很多的话，那么工作量也非常大，下面可以用这个脚本自动生成缺失的索引

/******************************************************************************************* 
--脚本功能描述： 
--  对于数据库中外键缺少索引的字段，生成对应的索引（排除一些系统账号，例如sys、system）,如果外键索引超过十个字段 
--  那么这个脚本就不能正确的生成对应的索引，当然也很少有外键设置在超过10个字段的。另外索引表空 
--  空间跟数据表空间相同，如有分开的话，建议在此处再做调整。 
********************************************************************************************/ 
SELECT    'CREATE INDEX ' || OWNER || '.' || REPLACE(CONSTRAINT_NAME,'FK_','IX_') ||  
        ' ON ' || OWNER || '.' || TABLE_NAME || ' (' || COL_LIST ||') TABLESPACE '  
        || (SELECT TABLESPACE_NAME FROM DBA_TABLES WHERE OWNER= CON.OWNER AND TABLE_NAME= CON.TABLE_NAME)  
        AS  CREATE_INDEXES_ON_FOREIGN_KEY  
FROM 
     (SELECT CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME, 
           MAX(DECODE(POSITION, 1,     '"' || 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 2,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 3,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 4,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 5,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 6,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 7,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 8,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 9,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) || 
           MAX(DECODE(POSITION, 10,', '||'"'|| 
                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST 
           FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CC 
           WHERE DC.OWNER = CC.OWNER 
           AND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME 
           AND DC.CONSTRAINT_TYPE = 'R' 
           AND DC.OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS', 'ADMIN') 
           GROUP BY CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME  
     ) CON 
      WHERE NOT EXISTS ( 
        SELECT 1 FROM 
                  ( SELECT TABLE_OWNER, TABLE_NAME,    
                       MAX(DECODE(COLUMN_POSITION, 1,     '"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 2,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 3,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 4,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 5,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 6,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 7,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 8,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 9,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) || 
                       MAX(DECODE(COLUMN_POSITION, 10,', '||'"'|| 
                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) COL_LIST 
                       FROM DBA_IND_COLUMNS  
                   WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS') 
                   GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COL 
    WHERE CON.OWNER = COL.TABLE_OWNER  
    AND CON.TABLE_NAME = COL.TABLE_NAME   
    AND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) )  ;

--脚本使用分析函数LISTAGG，适用于ORACLE 11g以及以上版本，如果数据库版本是Oracle 11g及以上，就可以使用此脚本替代上面脚本。

SELECT 'CREATE INDEX '  
              || OWNER  
              || '.'  
              || REPLACE(CONSTRAINT_NAME,'FK_','IX_')  
              || ' ON '  
              || OWNER  
              || '.'  
              || TABLE_NAME  
              || ' ('  
              || FK_COLUMNS  
              ||') TABLESPACE '  
              ||  
       (  
              SELECT TABLESPACE_NAME  
              FROM   DBA_TABLES  
              WHERE  OWNER= CON.OWNER  
              AND    TABLE_NAME= CON.TABLE_NAME) CREATE_INDEXES_ON_FOREIGN_KEY  
FROM   (  
                SELECT   CC.OWNER,  
                         CC.TABLE_NAME,  
                         CC.CONSTRAINT_NAME,  
                         LISTAGG(CC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY CC.POSITION) FK_COLUMNS 
                FROM     DBA_CONS_COLUMNS CC,  
                         DBA_CONSTRAINTS DC  
                WHERE    CC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME  
                AND      DC.CONSTRAINT_TYPE = 'R'  
                AND      CC.OWNER = DC.OWNER  
                AND      DC.OWNER NOT IN ( 'SYS',  
                                          'SYSTEM',  
                                          'OLAPSYS',  
                                          'SYSMAN',  
                                          'MDSYS',  
                                          'ADMIN' )  
                GROUP BY CC.OWNER,  
                         CC.TABLE_NAME,  
                         CC.CONSTRAINT_NAME) CON  
  WHERE NOT EXISTS  
       (  
              SELECT 1  
              FROM   (  
                              SELECT   TABLE_OWNER,  
                                       TABLE_NAME,  
                                       INDEX_NAME, 
                                       LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) FK_COLUMNS 
                              FROM     DBA_IND_COLUMNS  
                              WHERE    INDEX_OWNER NOT IN ( 'SYS',  
                                                           'SYSTEM',  
                                                           'OLAPSYS',  
                                                           'SYSMAN',  
                                                           'MDSYS',  
                                                           'ADMIN' )  
                                                          
                              GROUP BY TABLE_OWNER,  
                                       TABLE_NAME ,INDEX_NAME) COL  
              WHERE  CON.OWNER = COL.TABLE_OWNER  
              AND    CON.TABLE_NAME = COL.TABLE_NAME  
              AND    CON.FK_COLUMNS = SUBSTR(COL.FK_COLUMNS, 1, LENGTH(CON.FK_COLUMNS)) )  
              ORDER BY 1;

参考资料：

http://www.dba-oracle.com/t_foreign_key_indexing.htm

本文转载自网络，原文链接：https://mp.weixin.qq.com/s/_bQBQstlhsy8HdBikcTbog
本站部分内容转载于网络，版权归原作者所有，转载之目的在于传播更多优秀技术内容，如有侵权请联系QQ/微信：153890879删除，谢谢！

上一篇：MySQL随机恢复的几个段位 下一篇：年终盘点：2020数据库“四大”关键词

随机推荐

.NET Core 处理 WebAPI JSON 返回烦人的n

前言项目开发中不管是前台还是后台都会遇到烦人的null，数据库表中字段允许空值...
OBLOG4.0 OBLOG4.5漏洞利用分析

来源：DeepenStudy 漏洞文件：js.asp % Dimoblog setoblog=newclass_sys oblog.a...
深入了解MySQL主从复制的原理

本文转载自微信公众号「SH的全栈笔记」，作者SH。转载本文请联系SH的全栈笔记公...
flex项目中server的名称修改方法探讨

问题：我们在做flex的开发中，如果用到别人搭建好的框架，而别人的server名称往...
面试官：能说一说MySQL缓存池吗？

大家好，我是狂聊君。今天来聊一聊 Mysql 缓存池原理。提纲附上，话不多说，直...
最新IntelliJ IDEA 2020.2永久激活码(亲

idea官方推送了2020.2.4版本的更新，那么大家最关心的问题来了，之前激活idea202...
flash 报错捕获(Catch All Exception in

在Flash Player 10.1及以上版本中，adobe新增了全局错误处理程序UncaughtErrorEv...
AJAX+Servlet实现的数据处理显示功能示例

本文实例讲述了AJAX+Servlet实现的数据处理显示功能。分享给大家供大家参考，具...
巧用Spt_Values解决SQL中的连续日期问题

本文转载自微信公众号「SQL数据库」，作者丶平凡世界。转载本文请联系开发公众...
关于CKeditor的非主流个性应用的设置

CKeditor，以前叫FCKeditor，已经使用过好多年了，功能自然没的说。最近升级到3....

Oracle中关于外键缺少索引的探讨和总结

推荐图文

详解ASP.NET Core3.0 配置的Options模式

XML和JSP的联手

table中cesllspacing与cellpadding的区别详解

基于uni-app编写的登录模板，request请求封装，全局

QQ经典说说大全：爱笑的男生不会差，逗你笑的男生更

产品经理面试——简历填写

随机推荐

.NET Core 处理 WebAPI JSON 返回烦人的n

OBLOG4.0 OBLOG4.5漏洞利用分析

深入了解MySQL主从复制的原理

flex项目中server的名称修改方法探讨

面试官：能说一说MySQL缓存池吗？

最新IntelliJ IDEA 2020.2永久激活码(亲

flash 报错捕获(Catch All Exception in

AJAX+Servlet实现的数据处理显示功能示例

巧用Spt_Values解决SQL中的连续日期问题

关于CKeditor的非主流个性应用的设置

关于我们