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/