找出未创建索引的外键

1.环境准备

sys@ORCL>create table p(x int primary key);

sys@ORCL>create table c(x references p);

 

2.利用脚本查找为创建索引的外键

set linesize 999

 

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)

/

 

 

TABLE_NAME                     CONSTRAINT_NAME                COLUMNS

------------------------------ ------------------------------

C                              SYS_C0011099                   X

 

 

#####上面脚本中，处理的外键约束中，最多可以有8列；另外这个查询有一个前提，假设约束的所有者也是表和索引的所有者。

 

 

3.创建外键列索引

create index inx_c_x on c(x);

 

再次查询后，就查询不到记录咯！！

 

 

为什么要找外键未加索引的列：

1> 父表的更新操作可能会将子表锁住，进而容易出现死锁发生。

2> 查询表效率低下。再如两个表做了on delete cascade级联，当父表delete操作会伴随这一个子表的全表扫描。

 

 

 

reference                                              Thomas Kyte 编程艺术

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2122203/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2122203/