建立唯一索引遇到 ORA-01452: cannot create unique index; duplicate keys found,必須先清理重複資料。以 code_ref 表為例,三種定位重複列的方法:
-- 保留 ROWID 最大者 (最新插入),刪除其餘
DELETE FROM code_ref a
WHERE rowid > (
SELECT MIN(rowid)
FROM code_ref b
WHERE a.key_col1 = b.key_col1
AND a.key_col2 = b.key_col2
);
ROWID 為物理地址唯一值,MIN/MAX 可直接定位重複群組內的保留/刪除目標,無需排序。
-- 查找重複鍵值
SELECT key_col1, key_col2, COUNT(*)
FROM code_ref
GROUP BY key_col1, key_col2
HAVING COUNT(*) > 1;
-- 配合刪除 (需子查詢)
DELETE FROM code_ref
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM code_ref
GROUP BY key_col1, key_col2
);
@?/rdbms/admin/utlexcpt.sql (建立 EXCEPTIONS 表)ALTER TABLE code_ref ADD CONSTRAINT uk_code_ref UNIQUE (key_col1, key_col2)
EXCEPTIONS INTO EXCEPTIONS;
SELECT c.*
FROM code_ref c, EXCEPTIONS e
WHERE c.rowid = e.row_id
AND e.constraint = 'UK_CODE_REF';
| 方法 | 適用場景 | 優點 | 缺點 |
|---|---|---|---|
| ROWID 自連接 | 大表、頻繁清理 | 單掃描、無排序、效能最好 | SQL 較抽象 |
| GROUP BY | 中小表、一次性清理 | 語義清晰、易維護 | 大表可能產生大量排序/雜湊 |
| EXCEPTIONS | 需記錄違規細節、舊版相容 | 可捕獲約束名稱、錯誤類型 | 需額外建表、兩階段操作 |
清理後務必重新收集統計資訊 (DBMS_STATS.GATHER_TABLE_STATS) 確保優化器擁有正確資訊。