從應用程式著手優化 Oracle 資料庫（完整版）

本文系統闡述資料庫生命週期三階段的優化成本差異，並詳細說明資料庫設計（正規化/反正規化）與 SQL 撰寫技巧對效能的決定性影響。

優化成本與收益曲線

• 設計階段：成本最低、收益最大。
• 開發階段：成本中等。
• 成品/上線後：成本最高、收益最小。

硬體/網路/OS/參數調校合計僅佔 40% 提升空間，應用與 SQL 優化佔 60%~80%。

資料庫設計策略

正規化（至 3NF）

優點：消除異常、易維護。缺點：多表 Join 降低查詢速度。

反正規化四大手法

1. 水平分割：按範圍/雜湊分區，提升單表掃描速度，但應用需感知分區。
2. 垂直分割：熱冷欄分離，減少列寬度，單次 I/O 讀更多行，適合同時存取熱/冷欄機率低的場景。
3. 保留冗餘欄位：避免頻繁 Join，需同步更新機制（觸發器/應用層），適用低變動欄位。
4. 增加衍生欄位：預先計算彙總值，大幅縮短報表運算時間。

SQL 優化重點：WHERE 子句陷阱

1. IS NULL / IS NOT NULL

含 NULL 欄位不進 B-Tree 索引，優化器拒絕使用。對策：預設值、函式式索引、NOT NULL 約束。

2. 欄位串接（||）

-- 無法用索引
WHERE first_name || ' ' || last_name = 'George Bush'
-- 可用索引
WHERE first_name = 'George' AND last_name = 'Bush'
-- 變數拆解
WHERE first_name = SUBSTR(:name,1,INSTR(:name,' ')-1)
  AND last_name  = SUBSTR(:name,INSTR(:name,' ')+1)

3. LIKE 前置萬用字元

WHERE last_name LIKE '%Bush%'  -- 全表掃描
WHERE last_name LIKE 'Bush%'   -- 可用索引

必須前置模糊時：Oracle Text、反向索引 + REVERSE()、函式式索引。

實務建議

• 每條上線 SQL 必經 EXPLAIN PLAN / AUTOTRACE 驗證。
• 建立 SQL 標準庫，定期審視 Top N 消耗語句。
• 開發階段導入靜態分析工具（如 SonarQube + PL/SQL Cop）。