Oracle 資料庫系統效能調優：從設計規範化到 SQL 寫法避坑指南

效能調優的黃金法則

統計顯示：硬體/網路/OS/參數調優僅貢獻 ~40% 效能提升，剩餘 60%~80% 來自應用程式與 SQL 調優。且越早介入 (設計階段) 成本越低、收益越高。

資料庫設計層面：規範化與反規範化平衡

規範化 (正規化)

滿足 3NF (第三正規化) 通常兼顧維護性與實務需求。缺點：多表關聯降低查詢速度。

反規範化技巧 (針對高頻查詢優化)

1. 水平分割：依列值範圍 (如時間) 將大表拆分，提升單表掃描速度，但應用需感知分區。
2. 垂直分割：將高頻存取欄位與低頻欄位分離，減少列寬度，單次 I/O 讀取更多列。適用於同時查詢不同分割表機率低的場景。
3. 保留冗餘欄位：避免頻繁 JOIN，但需同步更新機制，適用於低變動欄位。
4. 增加衍生欄位：預先計算彙總值，大幅縮短統計查詢時間。

SQL 撰寫避坑指南 (WHERE 子句為核心)

1. IS NULL / IS NOT NULL 導致全表掃描

含 NULL 欄位不進入 B-tree 索引。任何 WHERE 條件使用 IS [NOT] NULL，優化器放棄索引。
對策：預設值 + NOT NULL 約束、函數索引 CREATE INDEX idx ON t (NVL(col, 'DEFAULT'))、或改寫業務邏輯避免 NULL 查詢。

2. 欄位連接 (||) 使索引失效

-- 錯誤：無法使用 last_name 索引
SELECT * FROM employee WHERE first_name || ' ' || last_name = 'George Bush';

-- 正確：拆分條件
SELECT * FROM employee WHERE first_name = 'George' AND last_name = 'Bush';

-- 變數傳入時：用 SUBSTR/INSTR 拆解，但函數不可作用於索引欄
SELECT * FROM employee
WHERE first_name = SUBSTR(:name, 1, INSTR(:name, ' ')-1)
  AND last_name  = SUBSTR(:name, INSTR(:name, ' ')+1);

3. LIKE '%pattern%' 開頭萬用字元導致全表掃描

-- 無法使用索引
SELECT * FROM employee WHERE last_name LIKE '%Bush%';

-- 可使用索引 (後置萬用字元)
SELECT * FROM employee WHERE last_name LIKE 'Bush%';

若業務必須前置萬用字元，考慮 Oracle Text、函數索引或應用層快取。

4. ORDER BY 非索引欄位或含運算式

會觸發排序作業 (SORT ORDER BY)，消耗 PGA/臨時表空間。
對策：重寫 ORDER BY 使用索引欄、建立對應索引、絕對避免 ORDER BY col1 || col2 等運算式。

5. NOT / <> / != 導致索引失效

-- 無法使用索引
SELECT * FROM employee WHERE salary <> 3000;

-- 改寫為範圍查詢，可使用索引
SELECT * FROM employee WHERE salary < 3000 OR salary > 3000;

同理，NOT IN、NOT EXISTS 皆含否定邏輯，優化器傾向全表掃描。

6. IN vs EXISTS 子查詢效能差異

-- IN 子查詢：先執行子查詢建立臨時結果集，再驅動主查詢
SELECT * FROM t1 WHERE col1 IN (SELECT col2 FROM t2 WHERE ...);

-- EXISTS 相關子查詢：先驅動主查詢，逐行檢查子查詢是否存在，找到即停止
SELECT * FROM t1 WHERE EXISTS (SELECT 'X' FROM t2 WHERE t2.col2 = t1.col1 AND ...);

結論：EXISTS 通常優於 IN (尤其子查詢結果集大時)；NOT EXISTS 優於 NOT IN。善用相關子查詢並確保關聯欄位有索引。

總結

效能調優是系統工程：從邏輯/物理設計、參數配置、到每一條 SQL 的撰寫細節。養成「先看執行計畫、再寫 SQL」習慣，善用索引、分區、物化視圖與結果快取，才能在海量資料下維持穩定高效的響應能力。