Oracle 外鍵缺索引檢查 SQL：單一/所有 Schema 版本

Oracle 外鍵缺索引檢查 SQL：單一/所有 Schema 版本

外鍵欄位若無索引，父表刪除/更新主鍵時需全表掃描子表鎖定，造成嚴重效能問題。本文提供檢查「外鍵欄位組合是否有對應索引」的標準 SQL，含單一 Schema 版與全資料庫版 (排除系統 Schema)。

核心邏輯

1. 從 USER_CONS_COLUMNS + USER_CONSTRAINTS 取得所有 FK (CONSTRAINT_TYPE='R') 及其欄位順序
2. 從 USER_IND_COLUMNS 取得所有索引欄位組合
3. 判斷：FK 欄位清單 (前 N 個) 是否被某索引的前 N 個欄位完全覆蓋
4. 若無索引覆蓋 → 回傳該 FK 定義

單一 Schema 版本 (當前使用者)

SET LINESIZE 400
COL columns FORMAT A50 WRAP
COL table_name FORMAT A30 WRAP
COL constraint_name FORMAT A30 WRAP

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) AS 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
);

全資料庫版本 (DBA 視角，排除系統 Schema)

SET LINESIZE 500
COL owner FORMAT A15
COL columns FORMAT A60 WRAP
COL table_name FORMAT A30 WRAP
COL constraint_name FORMAT A40 WRAP

SELECT owner,
       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) AS columns
FROM (
  SELECT b.owner,
         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 dba_cons_columns
    WHERE owner NOT IN ('SYS','SYSTEM','SYSMAN','HR','OE','EXFSYS','DBSNMP','MDSYS','OLAPSYS','SCOTT','SH','PM','CTXSYS')
  ) a,
  dba_constraints b
  WHERE a.constraint_name = b.constraint_name
    AND b.constraint_type = 'R'
  GROUP BY b.owner, b.table_name, b.constraint_name
) cons
WHERE col_cnt > ALL (
  SELECT COUNT(*)
  FROM dba_ind_columns i
  WHERE i.table_name = cons.table_name
    AND i.table_owner = cons.owner
    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
);

結果解讀與處理

對輸出的每一筆，建立對應索引：

SELECT a.index_name, a.table_name, a.column_name, a.column_position
FROM user_ind_columns a, user_constraints b
WHERE b.table_name = 'CHILD_TABLE'
  AND b.constraint_type = 'R'
  AND b.r_constraint_name = a.index_name(+)
ORDER BY a.index_name, a.column_position;

最佳實踐建議

1. 預設建立：建立 FK 時同步建立對應索引
2. 定期掃描：納入每週維護排程，異動表結構後必跑
3. 複合 FK：索引欄位順序必須與 FK 定義順序一致
4. 避免過度索引：唯讀/極少寫入的子表可評估不建索引