本文转载自微信公众号「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