Oracle 實戰經驗六則：HAVING、外部連接、去重、大事務、索引碎片、遷移重建

1. HAVING 子句正確用法

HAVING 作用於 GROUP BY 分組後，條件僅能包含：常數、聚合函數、GROUP BY 欄位。不可引用非分組欄位。

2. 外部連接 (+) 替代 NOT IN 提升效能

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

外部連接將反向查詢轉為正向 Join，利用索引大幅降低邏輯 I/O。

3. 大表去重：ROWID 最大值法與分批次策略

-- 標準寫法（50萬以上極慢）
DELETE FROM table_name a
WHERE rowid < (SELECT MAX(rowid) FROM table_name 
               WHERE col1=a.col1 AND col2=a.col2 AND col3=a.col3);

大表優化方案：

1. 建立臨時表 CREATE TABLE tmp AS SELECT DISTINCT ... FROM big_table
2. TRUNCATE TABLE big_table
3. INSERT /*+ APPEND */ INTO big_table SELECT * FROM tmp
4. 重建索引、約束、授權

4. 大事務指定回滾段避免 ORA-01555

SET TRANSACTION USE ROLLBACK SEGMENT roll_abc;
DELETE FROM huge_table WHERE ...;
COMMIT;

事務結束後自動解除綁定。需預先建立大尺寸回滾段（OPTIMAL 設定）。

5. 索引使用鐵律與碎片整理

• 20% 法則：子查詢回傳行數 > 表總行數 20% 時，全表掃描優於索引
• 碎片成因：刪除操作釋放表空間可重用，但索引空間不可重用，形成稀疏葉塊
• 整理策略：
  1. 高頻刪除表：定期 ALTER INDEX ... REBUILD（ONLINE 選項不阻塞 DML）
  2. 允許停機窗口：TRUNCATE TABLE 同步清除表與索引碎片

6. 資料庫遷移重建：分離結構與資料兩階段導入

# 階段 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

解決視圖依賴基礎表尚未建立導致創建失敗的問題。buffer=64000 提高陣列獲取效能，commit=Y 定期提交避免回滾段暴漲。

結語

六則經驗涵蓋 SQL 改寫、大物件維護、事務控制、索引生命週期、遷移策略，均源自電信計費等高載系統實戰。建議納入團隊「開發規範與運維手冊」雙軌管控。