找出oracle没有创建索引的外键

外键列没有索引，容易导致enq: TM - contention等待事件，所以外键列必须创建索引。

（1）下面的sql语句可以定位出哪些外键约束没有创建索引。

SELECT table_name, constraint_name, 
         cname1 
      || NVL2 (cname2, ',' || cname2, NULL) 
      || NVL2 (cname3, ',' || cname3, NULL) 
      || NVL2 (cname4, ',' || cname4, NULL) 
      || NVL2 (cname5, ',' || cname5, NULL) 
      || NVL2 (cname6, ',' || cname6, NULL) 
      || NVL2 (cname7, ',' || cname7, NULL) 
       || NVL2 (cname8, ',' || cname8, NULL) COLUMNS 
  FROM (SELECT   b.table_name, b.constraint_name, 
                 MAX (DECODE (POSITION, 1, column_name, NULL)) cname1, 
                 MAX (DECODE (POSITION, 2, column_name, NULL)) cname2, 
                 MAX (DECODE (POSITION, 3, column_name, NULL)) cname3, 
                 MAX (DECODE (POSITION, 4, column_name, NULL)) cname4, 
                 MAX (DECODE (POSITION, 5, column_name, NULL)) cname5, 
                 MAX (DECODE (POSITION, 6, column_name, NULL)) cname6, 
                 MAX (DECODE (POSITION, 7, column_name, NULL)) cname7, 
                 MAX (DECODE (POSITION, 8, column_name, NULL)) cname8, 
                 COUNT (*) col_cnt 
            FROM (SELECT SUBSTR (table_name, 1, 30) table_name, 
                         SUBSTR (constraint_name, 1, 30) constraint_name, 
                         SUBSTR (column_name, 1, 30) column_name, POSITION 
                    FROM user_cons_columns) a, 
                 user_constraints b 
           WHERE a.constraint_name = b.constraint_name 
             AND b.constraint_type = 'R' 
        GROUP BY b.table_name, b.constraint_name) cons 
 WHERE col_cnt > 
          ALL (SELECT   COUNT (*) 
                   FROM user_ind_columns i 
                  WHERE i.table_name = cons.table_name 
                    AND i.column_name IN 
                           (cname1, 
                            cname2, 
                            cname3, 
                            cname4, 
                            cname5, 
                            cname6, 
                            cname7, 
                            cname8 
                           ) 
                    AND i.column_position <= cons.col_cnt 
               GROUP BY i.index_name) 
/ 

（2）批量给没有索引的外键列创建索引

select 'create index ind_fk_'||table_name||' on '||table_name||' ('||columns||') online nologging;' from (
SELECT table_name, constraint_name, 
         cname1 
      || NVL2 (cname2, ',' || cname2, NULL) 
      || NVL2 (cname3, ',' || cname3, NULL) 
      || NVL2 (cname4, ',' || cname4, NULL) 
      || NVL2 (cname5, ',' || cname5, NULL) 
      || NVL2 (cname6, ',' || cname6, NULL) 
      || NVL2 (cname7, ',' || cname7, NULL) 
       || NVL2 (cname8, ',' || cname8, NULL) COLUMNS 
  FROM (SELECT   b.table_name, b.constraint_name, 
                 MAX (DECODE (POSITION, 1, column_name, NULL)) cname1, 
                 MAX (DECODE (POSITION, 2, column_name, NULL)) cname2, 
                 MAX (DECODE (POSITION, 3, column_name, NULL)) cname3, 
                 MAX (DECODE (POSITION, 4, column_name, NULL)) cname4, 
                 MAX (DECODE (POSITION, 5, column_name, NULL)) cname5, 
                 MAX (DECODE (POSITION, 6, column_name, NULL)) cname6, 
                 MAX (DECODE (POSITION, 7, column_name, NULL)) cname7, 
                 MAX (DECODE (POSITION, 8, column_name, NULL)) cname8, 
                 COUNT (*) col_cnt 
            FROM (SELECT SUBSTR (table_name, 1, 30) table_name, 
                         SUBSTR (constraint_name, 1, 30) constraint_name, 
                         SUBSTR (column_name, 1, 30) column_name, POSITION 
                    FROM user_cons_columns) a, 
                 user_constraints b 
           WHERE a.constraint_name = b.constraint_name 
             AND b.constraint_type = 'R' 
        GROUP BY b.table_name, b.constraint_name) cons 
 WHERE col_cnt > 
          ALL (SELECT   COUNT (*) 
                   FROM user_ind_columns i 
                  WHERE i.table_name = cons.table_name 
                    AND i.column_name IN 
                           (cname1, 
                            cname2, 
                            cname3, 
                            cname4, 
                            cname5, 
                            cname6, 
                            cname7, 
                            cname8 
                           ) 
                    AND i.column_position <= cons.col_cnt 
               GROUP BY i.index_name) 
)

/