SQL test 04 - OR Expansion 與 INLIST ITERATOR (有 Index)
#
在上一篇 [SQL test 03] 中我們看到了，如果查詢條件「沒有 Index」，Oracle 為了避免做兩次全表掃描，會拒絕執行 OR Expansion。
那麼，如果我們幫查詢條件加上了 Index，優化器又會怎麼做呢？
這篇文章我們將在「有 Index」的情況下，測試 WHERE ... OR ... 的效能與執行計畫。
建立 Index
#
先針對 LOCATION 與 GENDER 兩個欄位分別建立 B-Tree Index，以模擬優化器有 Index 可以利用的情境。
1
2
|
CREATE INDEX USERS_DETAILS_2023_IDX1 ON USERS_DETAILS_2023 (LOCATION);
CREATE INDEX USERS_DETAILS_2023_IDX2 ON USERS_DETAILS_2023 (GENDER);
|
測試情境
#
測試 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
19
20
21
22
23
24
|
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ PLAN TABLE OUTPUT │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Plan hash value: 2998711674 │
│ │
│ ------------------------------------------------------------------------------------------------------------ │
│ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | │
│ ------------------------------------------------------------------------------------------------------------ │
│ | 0 | SELECT STATEMENT | | 1 | 11 | 392 (1)| 00:00:01 | │
│ | 1 | SORT AGGREGATE | | 1 | 11 | | | │
│ | 2 | BITMAP CONVERSION COUNT | | 126K| 1364K| 392 (1)| 00:00:01 | │
│ | 3 | BITMAP OR | | | | | | │
│ | 4 | BITMAP CONVERSION FROM ROWIDS| | | | | | │
│ |* 5 | INDEX RANGE SCAN | USERS_DETAILS_2023_IDX2 | | | 389 (1)| 00:00:01 | │
│ | 6 | BITMAP CONVERSION FROM ROWIDS| | | | | | │
│ |* 7 | INDEX RANGE SCAN | USERS_DETAILS_2023_IDX1 | | | 3 (0)| 00:00:01 | │
│ ------------------------------------------------------------------------------------------------------------ │
│ │
│ Predicate Information (identified by operation id): │
│ --------------------------------------------------- │
│ │
│ 5 - access("GENDER"=U'Male') │
│ 7 - access("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: 242963651 │
│ │
│ ----------------------------------------------------------------------------------------------- │
│ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | │
│ ----------------------------------------------------------------------------------------------- │
│ | 0 | SELECT STATEMENT | | 2 | 18 | 3819 (1)| 00:00:01 | │
│ | 1 | UNION-ALL | | | | | | │
│ | 2 | SORT AGGREGATE | | 1 | 7 | | | │
│ |* 3 | INDEX RANGE SCAN | USERS_DETAILS_2023_IDX1 | 3 | 21 | 3 (0)| 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 - access("LOCATION"=U'Calgary, AB') │
│ 5 - filter("GENDER"=U'Male' AND LNNVL("LOCATION"=U'Calgary, AB')) │
└─────────────────────────────────────────────────────────────────────────────────────────────────┘
|
觀察與總結
#
從執行計畫中，我們可以清楚看到巨大的成本差異：
- SQL 1 (一般 OR)：使用了
BITMAP OR，Cost 為 392。
- SQL 2 (手動展開)：使用了
INDEX RANGE SCAN 加上 TABLE ACCESS FULL，Cost 為 3819。
Tuning 核心觀念
#
可以看到，手動展開成 UNION ALL 的 SQL 2 的 Cost 居然是 SQL 1 的將近 10 倍！為什麼會這樣？
在上一篇 [SQL test 03] 中我們學到，沒有 Index 時，拆成 UNION ALL 會導致做兩次全表掃描。
那這次我們明明在 LOCATION 和 GENDER 都加上了 Index，為什麼手動做 OR Expansion 的成本還是比較高呢？
這背後有兩個深刻的 Tuning 知識點：
1. 高成本的回表查詢 (Table Access By Index ROWID)
#
在 SQL 2 的第二個查詢中：GENDER = 'Male' AND LNNVL(LOCATION = 'Calgary, AB')。
雖然 GENDER 有 Index，但 Male 的資料量非常龐大 (約 12.6 萬筆)。如果走 Index，就必須透過 ROWID 回去原始資料表（回表）拿 LOCATION 欄位來做 LNNVL 驗證。這會產生高達十幾萬次的「隨機讀取 (Random I/O)」，成本遠比循序讀取還要高！
因此，聰明的優化器在第二個分支直接放棄使用 GENDER 的 Index，改採「全表掃描 (TABLE ACCESS FULL)」。這也導致 SQL 2 的總成本飆升到 3819。
2. 強大的 BITMAP OR (位元圖轉換)
#
針對 SQL 1，優化器找到了一條更棒的捷徑：Bitmap Conversion (位元圖轉換)。
既然 LOCATION 和 GENDER 都有獨立的 B-Tree Index，Oracle 可以將這兩個 Index 分別掃描出來的 ROWID，在記憶體中動態轉換成 Bitmap (位元圖)。接著直接對兩張 Bitmap 做 OR 邏輯運算（位元運算速度極快），最後再計算出總筆數（BITMAP CONVERSION COUNT）。
整個過程幾乎不需要去撈取龐大的原始資料表，因此 Cost 僅有 392，達到了極致的效能！
結論：
有了 Index 之後，Oracle 優化器依然會根據資料的分佈狀態（例如 Male 資料佔比極大、回表成本太高）來精算成本。在這個案例中，優化器完美運用了 BITMAP OR 的黑科技，秒殺了傳統的 UNION ALL 展開。
這再次呼應了我們的核心觀念：盡可能在過濾條件上提供足夠的「武器 (Index)」，然後信任資料庫強大的優化器 (CBO)，通常就能得到最佳的執行路徑！
測試完成後動作
#
刪除 index
1
2
|
DROP INDEX USERS_DETAILS_2023_IDX1;
DROP INDEX USERS_DETAILS_2023_IDX2;
|
