外键创建索引建议（Foreign Key Indexing Tips）

虽然增加索引，可能会带来一些额外的性能开销（DML操作开销增加）和磁盘空间方面的开销，
但是相比其带来的性能改善而言，这些额外的开销其实完全可以忽略。如果没有其他特殊情况，
建议所有的外键字段都加上索引。在Oracle Oracle Database 9i/10g/11g编程艺术这本书中介绍了
在什么时候不需要对外键加索引. 必须满足下面三个条件：

1: 不会删除父表中的行。
2：不论是有意还是无意，总之不会更新父表的唯一/主键字段值。
3：不会从父表联结到子表， 或者更通俗的讲，外键列不支持子表的一个重要访问路径，
而且你在谓词中没有使用这些外键累从子表中选择数据。


找出未索引的外键
--查看整个数据库下拥有主外键关系的所有表（排除一些系统用户）
SELECT DC.OWNER AS "PARENT_TABLE_OWNER",
DC.TABLE_NAME AS "PARENT_TABLE_NAME",
DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",
DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",
DF.OWNER AS "CHILD_TABLE_OWNER",
DF.TABLE_NAME AS "CHILD_TABLE_NAME"
FROM DBA_CONSTRAINTS DC,
(SELECT C.OWNER, C.CONSTRAINT_NAME, C.R_CONSTRAINT_NAME, C.TABLE_NAME
FROM DBA_CONSTRAINTS C
WHERE CONSTRAINT_TYPE = 'R') DF
WHERE DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME
AND DC.OWNER NOT IN ('SYSTEM',
'SYS',
'DBSNMP',
'EXFSYS',
'ORDDATA',
'CTXSYS',
'OLAPSYS',
'MDSYS',
'SYSMAN');

--查看某个Schema下拥有主外键关系的所有表
SELECT DC.OWNER AS "PARENT_TABLE_OWNER",
DC.TABLE_NAME AS "PARENT_TABLE_NAME",
DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",
DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",
DF.OWNER AS "CHILD_TABLE_OWNER",
DF.TABLE_NAME AS "CHILD_TABLE_NAME"
FROM DBA_CONSTRAINTS DC,
(SELECT C.OWNER,
C.CONSTRAINT_NAME,
C.R_CONSTRAINT_NAME,
C.TABLE_NAME
FROM DBA_CONSTRAINTS C
WHERE CONSTRAINT_TYPE = 'R') DF
WHERE DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME
AND DC.OWNER =UPPER('&OWNER')
order by DC.OWNER;


--查看某个具体的表是否和其它表拥有主外键关系
SELECT DC.OWNER AS "PARENT_TABLE_OWNER",
DC.TABLE_NAME AS "PARENT_TABLE_NAME",
DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME",
DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME",
DF.OWNER AS "CHILD_TABLE_OWNER",
DF.TABLE_NAME AS "CHILD_TABLE_NAME"
FROM DBA_CONSTRAINTS DC,
(SELECT C.OWNER,
C.CONSTRAINT_NAME,
C.R_CONSTRAINT_NAME,
C.TABLE_NAME
FROM DBA_CONSTRAINTS C
WHERE CONSTRAINT_TYPE = 'R') DF
WHERE DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME
AND DC.OWNER =UPPER('&OWNER')
AND DC.TABLE_NAME=UPPER('&TABLE_NAME');


接下来我们要找出在具体的外键字段是否有索引
SELECT CON.OWNER ,
CON.TABLE_NAME,
CON.CONSTRAINT_NAME,
CON.COL_LIST,
'No Indexed' AS INDEX_STATUS
FROM
(SELECT CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME,
MAX(DECODE(POSITION, 1, '"' ||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 2,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 3,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 4,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 5,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 6,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 7,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 8,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 9,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(POSITION, 10,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST
FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CC
WHERE DC.OWNER = CC.OWNER
AND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
AND DC.CONSTRAINT_TYPE = 'R'
AND DC.OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS', 'ADMIN')
GROUP BY CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME
) CON
WHERE NOT EXISTS (
SELECT 1 FROM
( SELECT TABLE_OWNER, TABLE_NAME,
MAX(DECODE(COLUMN_POSITION, 1, '"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 2,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 3,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 4,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 5,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 6,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 7,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 8,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 9,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||
MAX(DECODE(COLUMN_POSITION, 10,', '||'"'||
SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST
FROM DBA_IND_COLUMNS
WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS')
GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COL
WHERE CON.OWNER = COL.TABLE_OWNER
AND CON.TABLE_NAME = COL.TABLE_NAME
AND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST)))
order by CON.OWNER;


如果是ORACLE 11g或以上版本，数据库有分析函数LISTAGG的话，可以使用下面脚本
SELECT CASE
WHEN B.TABLE_NAME IS NULL THEN 'NO INDEXED'
ELSE 'INDEXED'
END AS STATUS,
A.TABLE_OWNER AS TABLE_OWNER,
A.TABLE_NAME AS TABLE_NAME,
A.CONSTRAINT_NAME AS FK_NAME,
A.FK_COLUMNS AS FK_COLUMNS,
B.INDEX_NAME AS INDEX_NAME,
B.INDEX_COLUMNS AS INDEX_COLUMNS
FROM (SELECT A.OWNER AS TABLE_OWNER,
A.TABLE_NAME AS TABLE_NAME,
A.CONSTRAINT_NAME AS CONSTRAINT_NAME,
LISTAGG(A.COLUMN_NAME, ',')
WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS
FROM DBA_CONS_COLUMNS A,
DBA_CONSTRAINTS B
WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'R'
AND A.OWNER = B.OWNER
AND A.OWNER NOT IN ( 'SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN',
'MDSYS' )
GROUP BY A.OWNER,
A.TABLE_NAME,
A.CONSTRAINT_NAME) A,
(SELECT TABLE_OWNER,
TABLE_NAME,
INDEX_NAME,
LISTAGG(C.COLUMN_NAME, ',')
WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS
FROM DBA_IND_COLUMNS C
GROUP BY TABLE_OWNER,
TABLE_NAME,
INDEX_NAME) B
WHERE A.TABLE_NAME = B.TABLE_NAME(+)
AND A.TABLE_OWNER = B.TABLE_OWNER(+)
AND B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS || '%'
ORDER BY 1, A.TABLE_OWNER DESC;