Oracle 數據庫實戰經驗六則：SQL 優化與維護技巧

技巧 1：HAVING 子句正確用法

HAVING 用於過濾 GROUP BY 分組結果，條件中僅能引用：常量、聚合函數、GROUP BY 列。不可直接引用非分組列。

技巧 2：外部連接「+」替代 NOT IN 提升性能

外連接「+」位於「=」右側為左連接，左側為右連接。利用外連接 + IS NULL 判斷可高效替代低效的 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';

實測運行速度明顯提升。

技巧 3：大表刪除重複記錄的高效方案

常規 ROWID 最大值法：

DELETE FROM table_name a
WHERE rowid < (SELECT MAX(rowid) FROM table_name
               WHERE col1=a.col1 AND col2=a.col2 ...);

缺點：50 萬行以上表性能極差。
替代方案：

• CTAS 建新表 CREATE TABLE new_t AS SELECT DISTINCT ... FROM old_t，再 DROP/RENAME。
• 利用 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY rowid) 標記保留行，單次掃描刪除。
• 分區表場景下採用 EXCHANGE PARTITION 零停機剔重。

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

長事務報 ORA-01555: snapshot too old 時，可顯式綁定大回滾段：

SET TRANSACTION USE ROLLBACK SEGMENT roll_big;
DELETE FROM huge_table WHERE ...;
COMMIT;  -- 提交後自動解除綁定

注意：SET TRANSACTION 必須為事務首條語句。

技巧 5：索引使用的三大避坑指南

1. 選擇性閾值：單表掃描超過 20% 行時，全表掃描優於索引。
2. 碎片管理：頻繁 DELETE 導致索引碎片，定期 ALTER INDEX ... REBUILD 或階段性 TRUNCATE TABLE（同時清理索引碎片）。
3. 函數/隱式轉換失效：WHERE UPPER(col) = 'ABC'、WHERE char_col = 123 均導致索引失效，改用函數索引或顯式轉換。

技巧 6：IMP 重建數據庫時視圖依賴順序問題

視圖創建可能早於基表導致失敗。採用「兩階段導入」：

-- 階段 1：僅導入結構（無數據）
imp userid/pwd@sid file=expdata.dmp rows=N

-- 階段 2：導入結構+數據，忽略已存在對象
imp userid/pwd@sid file=expdata.dmp full=Y buffer=64000 commit=Y ignore=Y

ignore=Y 確保二次導入不因對象存在而報錯。