Oracle 實戰技巧五則：外部連接、事務控制、索引優化、遷移重建與 HAVING 進階

技巧一：善用外部連接 (+) 替代低效 NOT IN

Oracle 傳統外部連接運算子 (+) 可大幅提升「不存在」類查詢效能。將 NOT IN 子查詢改寫為外部連接 + IS NULL 判斷，避開子查詢逐行掃描的效能陷阱。

-- 低效寫法
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';

外部連接讓優化器採用巢狀迴路或雜湊連接，效能通常提升數量級。

技巧二：SET TRANSACTION 指定大型回滾段解決 ORA-01555

大量交易執行時常遭遇 ORA-01555: snapshot too old，主因是隨機分配的回滾段過小。使用 SET TRANSACTION USE ROLLBACK SEGMENT 顯式指定大型回滾段：

SET TRANSACTION USE ROLLBACK SEGMENT roll_abc;
DELETE FROM large_table WHERE ...;
COMMIT;  -- 提交後自動釋放回滾段指定

此技巧在批次刪除、大量資料轉換等長時間交易中極為實用。

技巧三：索引使用的黃金法則與碎片治理

• 20% 選擇率門檻：子查詢回傳行數超過表總行數 20% 時，全表掃描優於索引掃描
• 碎片預防：頻繁 DELETE 的索引表需定期 REBUILD INDEX 或 TRUNCATE TABLE（同時清除索引碎片）
• 函數索引陷阱：WHERE 條件對索引欄位使用函數（如 TO_CHAR(date_col) = '2024'）會導致索引失效，需建立函數索引

技巧四：資料庫遷移重建的兩階段匯入法

使用 IMPORT 重建資料庫時，視圖建立順序可能早於基礎表導致失敗。採用「先結構後資料」兩階段策略：

-- 階段 1：僅匯入結構 (rows=n)
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 確保第二階段即使物件已存在也能繼續匯入資料。

技巧五：HAVING 子句的精確應用場景

HAVING 專門過濾 GROUP BY 分組結果，條件僅能包含：常數、聚合函數、或 GROUP BY 子句中的欄位。切勿混淆 WHERE（過濾原始行）與 HAVING（過濾分組）的執行順序差異。