SQL test 03 - OR Expansion (OR 展開)
#
在 SQL Tuning 中，遇到 WHERE ... OR ... 的條件時，Oracle 優化器有時會啟動一種稱為 OR Expansion (OR 展開) 的查詢轉換機制。它的原理是將一個帶有 OR 的查詢，拆分成多個使用 UNION ALL 的獨立查詢，以期能分別使用不同的 Index 來提升效能。
這篇文章我們將手動模擬 OR Expansion 的過程，並看看在「沒有 Index」的情況下，優化器為什麼會做出不展開的決定。
測試情境
#
測試 SQL 1 (一般 OR 寫法)
#
這是一般開發者最直覺的寫法。
1
2
3
|
SELECT COUNT(*)
FROM pollo.USERS_DETAILS_2023
where LOCATION = 'Calgary, AB' or GENDER = 'Male';
|
執行結果：
1
2
3
4
5
|
┌───────────┐
│ COUNT (*) │
├───────────┤
│ 126990 │
└───────────┘
|
測試 SQL 2 (手動模擬 OR Expansion)
#
我們將原本的 OR 拆分成兩個獨立的查詢，並用 UNION ALL 組合。
為了避免兩邊的條件有交集而導致資料重複計算，Oracle 在內部做 OR Expansion 時，通常會自動加上 LNNVL 函數來排除已經在第一個條件成立的資料。
1
2
3
|
select count(*) from pollo.USERS_DETAILS_2023 where LOCATION = 'Calgary, AB'
union all
select count(*) from pollo.USERS_DETAILS_2023 where GENDER = 'Male' and lnnvl(LOCATION = 'Calgary, AB');
|
執行結果：
1
2
3
4
5
6
|
┌───────────┐
│ COUNT (*) │
├───────────┤
│ 12 │
│ 126978 │
└───────────┘
|
(註：兩者相加 12 + 126978 剛好等於 SQL 1 的總數 126990)
執行計畫 (Explain Plan) 比較
#
SQL 1 的執行計畫 (未展開)
#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
┌───────────────────────────────────────────────────────────────────────────────────────────┐
│ PLAN TABLE OUTPUT │
├───────────────────────────────────────────────────────────────────────────────────────────┤
│ Plan hash value: 4071893995 │
│ │
│ ----------------------------------------------------------------------------------------- │
│ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | │
│ ----------------------------------------------------------------------------------------- │
│ | 0 | SELECT STATEMENT | | 1 | 11 | 3816 (1)| 00:00:01 | │
│ | 1 | SORT AGGREGATE | | 1 | 11 | | | │
│ |* 2 | TABLE ACCESS FULL| USERS_DETAILS_2023 | 126K| 1364K| 3816 (1)| 00:00:01 | │
│ ----------------------------------------------------------------------------------------- │
│ │
│ Predicate Information (identified by operation id): │
│ --------------------------------------------------- │
│ │
│ 2 - filter("GENDER"=U'Male' OR "LOCATION"=U'Calgary, AB') │
└───────────────────────────────────────────────────────────────────────────────────────────┘
|
SQL 2 的執行計畫 (手動展開)
#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
┌────────────────────────────────────────────────────────────────────────────────────────────┐
│ PLAN TABLE OUTPUT │
├────────────────────────────────────────────────────────────────────────────────────────────┤
│ Plan hash value: 1826431837 │
│ │
│ ------------------------------------------------------------------------------------------ │
│ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | │
│ ------------------------------------------------------------------------------------------ │
│ | 0 | SELECT STATEMENT | | 2 | 18 | 7631 (1)| 00:00:01 | │
│ | 1 | UNION-ALL | | | | | | │
│ | 2 | SORT AGGREGATE | | 1 | 7 | | | │
│ |* 3 | TABLE ACCESS FULL| USERS_DETAILS_2023 | 3 | 21 | 3815 (1)| 00:00:01 | │
│ | 4 | SORT AGGREGATE | | 1 | 11 | | | │
│ |* 5 | TABLE ACCESS FULL| USERS_DETAILS_2023 | 126K| 1364K| 3816 (1)| 00:00:01 | │
│ ------------------------------------------------------------------------------------------ │
│ │
│ Predicate Information (identified by operation id): │
│ --------------------------------------------------- │
│ │
│ 3 - filter("LOCATION"=U'Calgary, AB') │
│ 5 - filter("GENDER"=U'Male' AND LNNVL("LOCATION"=U'Calgary, AB')) │
└────────────────────────────────────────────────────────────────────────────────────────────┘
|
觀察與總結
#
從執行計畫中，我們可以清楚看到巨大的成本差異：
- SQL 1 (一般 OR)：只進行了 1 次
TABLE ACCESS FULL，Cost 為 3816。
- SQL 2 (手動展開)：進行了 2 次
TABLE ACCESS FULL，Cost 疊加為 3815 + 3816 = 7631。
Tuning 核心觀念
#
可以看到，手動展開後 SQL 2 的 Cost 足足是 SQL 1 的兩倍！為什麼會這樣？
這是因為本測試中，LOCATION 與 GENDER 欄位都沒有建立 Index。
如果沒有 Index，每一次的查詢分支都必須執行一次極度消耗資源的「全表掃描 (Full Table Scan)」。將條件展開成兩段 UNION ALL，就意味著資料庫要把整張大表從頭到尾掃描兩遍。
Oracle 的 Cost-Based Optimizer (CBO) 非常聰明，它在底層計算過成本後發現：「與其展開後掃描兩次大表，不如不展開，在一次全表掃描中同時判斷這兩個 OR 條件。」因此，優化器理智地不使用 OR Expansion。
結論：
OR Expansion 是一把雙面刃。它通常只在「OR 涉及的各個條件欄位都有適當的 Index」時，才能發揮威力。在缺乏 Index 的情況下，維持原來的 OR 寫法，讓資料庫一次掃描完成過濾，才是效能最佳的選擇。
