Oracle UNION ALL 實戰指南：替代 OR/IN、IS NULL 陷阱、複合索引首欄原則與排序消除

UNION 替代 OR：索引列條件分拆

對索引欄位使用 OR 會導致全表掃描。僅當所有 OR 條件欄位皆有索引時，UNION 改寫有效：

-- 高效：UNION 可分別使用 LOC_ID、REGION 索引
SELECT loc_id, loc_desc, region FROM location WHERE loc_id = 10
UNION
SELECT loc_id, loc_desc, region FROM location WHERE region = 'MELBOURNE';

-- 低效：OR 導致全表掃描
SELECT loc_id, loc_desc, region FROM location WHERE loc_id = 10 OR region = 'MELBOURNE';

注意：若任一欄位無索引，UNION 改寫反而可能降低效能。

IN 替代多值 OR

-- 低效
SELECT ... FROM location WHERE loc_id = 10 OR loc_id = 20 OR loc_id = 30;

-- 高效 (8i+ 執行計劃通常相同，但可讀性更好)
SELECT ... FROM location WHERE loc_id IN (10, 20, 30);

索引列 IS NULL/IS NOT NULL 陷阱

• 單欄索引：NULL 值 不存入索引，WHERE idx_col IS NULL 必須全表掃描
• 複合索引：全為 NULL 不存入，至少一欄非 NULL 則存入
• 唯一索引 (A,B) 允許插入多筆 (123, NULL) —— 因 NULL != NULL
• 解決：NVL(idx_col, '特殊值') + 函數索引，或設計預設值避免 NULL

複合索引首欄原則

• 索引定義順序：CREATE INDEX idx_ab ON t(a, b)
• WHERE 條件 必須包含首欄 (a) 優化器才會考慮使用該索引
• 僅引用第二欄 (b) → 全表掃描
• 此為「最左前綴原則」核心體現

UNION ALL 替代 UNION：消除排序開銷

決策流程

1. 業務是否允許重複結果？否 → UNION / UNION ALL + 外層 DISTINCT
2. 是否確定兩結果集無重複？是 → 直接 UNION ALL
3. 需去重且結果集大 → 考慮 HASH GROUP BY 或分頁處理
4. 監控 V$SQL_WORKAREA 觀察排序記憶體使用

SORT_AREA_SIZE 優化提醒

• UNION/ORDER BY/DISTINCT 觸發排序使用此記憶體區
• 手工 PGA 管理模式下調整此參數；自動 PGA 管理下由 PGA_AGGREGATE_TARGET 控制
• 排序溢寫臨時表空間會大幅降低效能

1. 上一篇文章： Oracle EXISTS 用法精要：替代 DISTINCT、索引維護與 SQL 撰寫規範
2. 下一篇文章： Oracle WHERE 子句索引使用鐵律：型別轉換、失效模式與資源耗盡操作避坑指南

Oracle 資料庫基礎

Oracle 忽略大小寫查詢三大策略：效能與正確性的權衡

Oracle 預設區分大小寫，若需實現大小寫不敏感查詢，常見

Oracle 密碼遺忘破解兩大招：SYSDBA 直連與 ALTER/CREATE USER 實戰

前提條件 作業系統帳號屬於 ORA_DBA 群組（Wind

Oracle 分頁查詢最佳實踐：ROWNUM 經典模式與 12c+ 新特性

Oracle 分頁查詢全解析：從經典三層嵌套到 FETCH

Oracle PL/SQL 發送 HTML 郵件完整實作：支援多收件人與 SMTP 驗證

需求背景 在資料庫層面直接發送郵件，可用於系統通知、報表分發