Oracle SQL 進階優化規則大全：操作符、函數、隱式轉換、通配符、UNION、SQL 寫法標準化、表連接順序完整指南

一、IS NULL/IS NOT NULL 進階改寫

-- 1. 以非空判斷替代 (需業務允許)
col IS NOT NULL  →  col > 0  或  col > ''

-- 2. 設計階段：NOT NULL + DEFAULT
ALTER TABLE t MODIFY status DEFAULT 'NEW' NOT NULL;

-- 3. 函數索引 (11g+)
CREATE INDEX idx_t_status_nvl ON t (NVL(status, 'NULL_MARK'));
WHERE NVL(status, 'NULL_MARK') = 'NULL_MARK'

-- 4. 位圖索引 (低基數、DW)
CREATE BITMAP INDEX idx_t_status_bmp ON t (status);

二、通配符與 LIKE 優化

• LIKE '%abc%'：全表掃描，不可避免 (除 Oracle Text)。
• LIKE 'abc%'：索引範圍掃描 (Index Range Scan)。
• 實戰：YY_BH LIKE '%5400%' → 改寫為 YY_BH LIKE 'X5400%' OR YY_BH LIKE 'B5400%' 走索引兩次範圍掃描。

三、連接操作符 || 導致索引失效

-- 失效
WHERE col1 || col2 = 'value'

-- 優化：拆分條件
WHERE col1 = 'val1' AND col2 = 'val2'

-- 或使用虛擬欄位 + 索引 (11g+)
ALTER TABLE t ADD (col12 AS (col1 || col2) VIRTUAL);
CREATE INDEX idx_t_col12 ON t (col12);

四、函數/運算導致索引失效

五、可能導致全表掃描的操作清單

1. 索引欄使用 NOT 或 <>
2. 索引欄使用函數/運算
3. NOT IN 子查詢
4. 通配符位於首位 LIKE '%...'
5. IS NULL / IS NOT NULL (單欄 B-Tree)
6. 複合索引前導欄未出現在 WHERE

六、UNION vs UNION ALL 強制規範

-- 業務確認無重複 (如當前表/歷史表、分區表)
SELECT * FROM gc_dfys UNION ALL SELECT * FROM ls_jg_dfys;
-- 禁用 UNION 除非確需去重

七、SQL 寫法標準化：共享游標與 SGA 效能

Oracle 對 完全相同字元串 (含大小寫、空格、換行、Schema 前綴) 才共享游標。

-- 這 4 句會產生 4 個 Parent Cursor，佔用 4 倍 Shared Pool
SELECT * FROM zl_yhjbqk;
SELECT * FROM zl_yhjbqk ;
SELECT * FROM DLYX.ZL_YHJBQK;
select * from zl_yhjbqk;

-- 規範：統一大寫、統一 Schema 前綴、統一格式化
SELECT * FROM DLYX.ZL_YHJBQK;

• 減少 Hard Parse、Library Cache 爭用、Shared Pool 碎片。
• 準確統計 SQL 執行頻率 (V$SQL/V$SQLAREA)。

八、WHERE 條件順序與表連接順序

WHERE 條件順序 (RBO / 早期 CBO)

• Oracle 自下而上解析 WHERE 條件 (舊版 RBO 行為)。
• 過濾率最高 (最少行) 的條件寫在 最末尾。
• 表連接條件寫在其他條件 之前。

-- 低效：99% 條件優先，大量無效比較
SELECT * FROM t WHERE dy_dj = '1KV以下' AND xh_bz = 1;

-- 高效：0.5% 條件優先，早期過濾
SELECT * FROM t WHERE xh_bz = 1 AND dy_dj = '1KV以下';

現代 CBO (10g+) 會自動根據統計資訊重排，但良好習慣仍有助穩定計畫。

FROM 表順序 (RBO)

• Oracle 自右向左處理 FROM 表 (RBO)。
• 小表 (驅動表) 放在 最右邊。
• 現代 CBO 會自動選擇驅動表 (統計資訊準確時)。

九、其他實戰技巧

• ORDER BY 非索引欄 → 建立索引或接受 Sort 開銷；嚴禁 ORDER BY expression。
• 多表連接 必用別名，欄位加前綴 t1.col，減少解析時間。
• 善用內建函數：NVL, COALESCE, CASE, DECODE, REGEXP_SUBSTR 替代複雜 PL/SQL。
• 執行計畫異常時，優先檢查：統計資訊、索引狀態、SGA/磁碟壓力、參數設定。