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

?

/* 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)/

?

?

?

?

?