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

Dear 丶 2022-06-17 05:08 179阅读 0赞

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

外键缺失索引影响

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

1.  影响性能。 如果子表外键没有创建索引，那么当父表查询关联子表时，子表将进行全表扫描。影响表连接方式。
2.  影响并发。 无论是更新父表主键，或者删除一个父记录，都会在子表中加一个表锁（在这条语句完成前，不允许对子表做任何修改）。这就会不必要

地锁定更多的行，而影响并发性

3：在特殊情况下，还有可能造成死锁。

我们先来看看一个简单的例子，看看当外键缺失索引时，子表是否进行全表扫描，如下所示，表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

![这里写图片描述][73542-20170417162937571-1678083074.png]  
如上所示，当外键字段没有索引时，父表与子表关联时，子表会进行全表扫描，下面，我在外键字段创建索引后，就能避免子表表扫描了。

CREATE INDEX SCOTT.IX\_DEPTNO ON SCOTT.EMP (“DEPTNO”) TABLESPACE USERS;  
![这里写图片描述][73542-20170417162941946-287864876.png]  
当然这两个表的数据量实在是太少了，性能上差别不大，当数据量增长上去后，这个性能差异就会比较明显了。如下例子所示，我们构造一个数据量相对较大的父表与子表：

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>

![这里写图片描述][73542-20170417162946196-237219524.png]  
创建索引后，我们再来看看其执行计划，注意对比创建索引前后，执行计划的差异，如下所示：

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;

![这里写图片描述][73542-20170417162948227-945893459.png]  
接下来，我们再来看看外键缺失索引影响并发，以及造成死锁的情况，如下所示，创建表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;

![这里写图片描述][73542-20170417162950556-704040844.png]  
上面信息如果不能让你理解，那么可以看看下面脚本，相信你能看得更详细。

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
           883 TEST     DB-Server.loca TX                      Exclusive       None                0
                        ldomain
    
           883 TEST     DB-Server.loca TM DEAD_LOCK_PARENT     Row Exlusive    None                0
                        ldomain
    
           883 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  
![这里写图片描述][73542-20170417162952274-1160125896.png]  
如果你在外键字段上创建索引，那么这种情况下的操作就不会出现死锁。在这里就不再赘述。有兴趣可以测试一下.

外键创建索引建议（Foreign Key Indexing Tips）

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

1: 不会删除父表中的行。

2： 不论是有意还是无意，总之不会更新父表的唯一/主键字段值。

3： 不会从父表联结到子表， 或者更通俗的讲，外键列不支持子表的一个重要访问路径，而且你在谓词中没有使用这些外键累从子表中选择数据。

找出未索引的外键

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

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

--查看整个数据库下拥有主外键关系的所有表（排除一些系统用户） 
    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.cnblogs.com/kerrycode/p/6723345.html][http_www.cnblogs.com_kerrycode_p_6723345.html]

[73542-20170417162937571-1678083074.png]: /images/20220617/39b9c1be2c814eddae0f9dc8aa64431e.png
[73542-20170417162941946-287864876.png]: /images/20220617/f36be20bf44044a4bd6f707f08f5a822.png
[73542-20170417162946196-237219524.png]: /images/20220617/2acf35948d0a4d628ce18728ad6b0a17.png
[73542-20170417162948227-945893459.png]: /images/20220617/b467728eae974c1a8f4140bf8b972178.png
[73542-20170417162950556-704040844.png]: /images/20220617/f847c699da084d51bd4fdce6e4c54f38.png
[73542-20170417162952274-1160125896.png]: /images/20220617/f46c00b9d8ce4c7d8710b4834e7d96d6.png
[http_www.cnblogs.com_kerrycode_p_6723345.html]: http://www.cnblogs.com/kerrycode/p/6723345.html