【技术】为没有索引的外键创建索引脚本

 

/* Formatted on 2011/05/04 15:11 (Formatter Plus v4.8.7) */
SELECT    'create index IX_'
       || table_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)
       || ' ON '
       || table_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)
/