Oracle 實戰精華：六大高效能 SQL 技巧與維護秘訣

技巧一：HAVING 子句精準過濾分組

HAVING 對 GROUP BY 結果集分組進行過濾，條件僅允許：常數、聚合函數、GROUP BY 中的欄位。

技巧二：外部聯結 (+) 替代 NOT IN 提升效能

低效寫法

SELECT a.empno FROM emp a
WHERE a.empno NOT IN (SELECT empno FROM emp1 WHERE job='SALE');

高效改寫（左外部聯結）

SELECT a.empno FROM emp a, emp1 b
WHERE a.empno = b.empno(+)
  AND b.empno IS NULL
  AND b.job = 'SALE';

原理：外部聯結保留主表所有行，無匹配時從表欄位為 NULL。利用索引驅動，速度大幅提升。

技巧三：刪除重複記錄三種方案

假設表 TBL，主鍵 (COL1, COL2)，有索引。

方案 1：臨時表法（大數據量慎用）

CREATE TABLE tbl_tmp AS SELECT DISTINCT * FROM tbl;
TRUNCATE TABLE tbl;
INSERT INTO tbl SELECT * FROM tbl_tmp;

⚠️ 千萬級資料生產環境不可行，IO 開銷過大。

方案 2：ROWID 自聯結（已知每筆僅一重複）

DELETE FROM tbl
WHERE rowid IN (
  SELECT a.rowid FROM tbl a, tbl b
  WHERE a.rowid > b.rowid
    AND a.col1 = b.col1 AND a.col2 = b.col2);

方案 3：MAX/MIN ROWID 保留法（通用）

-- 保留最大 ROWID
DELETE FROM tbl a
WHERE rowid NOT IN (
  SELECT MAX(b.rowid) FROM tbl b
  WHERE a.col1 = b.col1 AND a.col2 = b.col2);

-- 或使用 MIN 配合 >
DELETE FROM tbl a
WHERE rowid < (
  SELECT MAX(b.rowid) FROM tbl b
  WHERE a.col1 = b.col1 AND a.col2 = b.col2);

方案 4：GROUP BY 優化（大數據量首選）

DELETE FROM tbl
WHERE rowid NOT IN (
  SELECT MAX(rowid) FROM tbl t
  GROUP BY t.col1, t.col2);

-- 僅處理有重複的群組
DELETE FROM tbl
WHERE (col1, col2) IN (
  SELECT col1, col2 FROM tbl
  GROUP BY col1, col2 HAVING COUNT(*) > 1)
  AND rowid NOT IN (
    SELECT MIN(rowid) FROM tbl
    GROUP BY col1, col2 HAVING COUNT(*) > 1);

✅ 利用索引加速 GROUP BY，僅掃描重複群組，大表效能最佳。

技巧四：SET TRANSACTION 指定大型 Rollback Segment 避免 ORA-01555

大交易報錯：ORA-01555: snapshot too old (rollback segment too small)

解法：預先指定足夠大的 Rollback Segment

SET TRANSACTION USE ROLLBACK SEGMENT roll_abc;
DELETE FROM table_name WHERE ...;
COMMIT;  -- 交易結束自動釋放指定

技巧五：索引使用黃金法則

1. 選擇度原則：子查詢存取行數 < 20% 總行數時索引才有效；超過 20% 全表掃描更快
2. 碎片管理：頻繁 DELETE 導致索引碎片（空間不釋放），定期 REBUILD INDEX 或 TRUNCATE TABLE（同步清理索引碎片）

技巧六：資料庫重建避免 View 建立失敗

IMP 匯入時 View 可能因基底表尚未建立而失敗。雙階段策略：

# 階段 1：僅匯入結構（無資料）
imp jfcl/hfjf@ora1 file=expdata.dmp rows=N

# 階段 2：匯入結構+資料，忽略已存在物件
imp jfcl/hfjf@ora1 file=expdata.dmp full=Y buffer=64000 commit=Y ignore=Y

IGNORE=Y 確保第二階段不因物件存在而中斷，BUFFER=64000 每 64KB 提交一次加速。

總結

這六大技巧涵蓋查詢優化、資料清理、交易控制、索引維護、遷移部署核心場景，是 Oracle DBA 與開發者的必備工具箱。實戰中需結合執行計畫、統計資訊、業務特性靈活組合應用。