↓ 快轉到主要內容
PolloChang 工作筆記

SQL test 05 - View Merging & Query Transformation

·4920 字·10 分鐘·
Oracle SQL Tuning 學習筆記
PolloChang
作者
PolloChang
我是一隻雞
目錄

SQL test 05 - View Merging 與查詢轉換的奧秘
#

在撰寫複雜 SQL 時，我們經常會使用子查詢 (Subquery) 或是內嵌視圖 (Inline View) 來讓語法更容易閱讀，或者分階段處理資料。 但 Oracle 的優化器 (Optimizer) 會怎麼處理這些「視圖」呢？它會乖乖照著我們寫的層級去執行嗎？

這篇文章將帶你探討 Oracle 的兩大強大武器：Simple View Merging (簡單視圖合併) 以及 Group-By Placement / View Formation (聚合下推與視圖生成)。

1. Simple View Merging (簡單視圖合併)
#

簡單視圖合併通常發生在子查詢只包含 SELECT、FROM、WHERE 等基本操作時。優化器為了打破子查詢的限制，會將這些子查詢「攤平」，直接與外層的查詢合併成一個大型的 Join 樹。

測試 SQL 1 (使用 Inline View)
#

我們將 USERS_DETAILS_2023 和 USERS_SCORE_2023 寫在一個子查詢中，另外將 ANIME_DATASET_2023 寫在另一個子查詢中，最後再將它們 Join 起來。 (注意：這裡我們內部特地只選了 t2.* 輸出)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT *
FROM (
    SELECT t2.*
    FROM USERS_DETAILS_2023 t1, USERS_SCORE_2023 t2
    WHERE t1.MAL_ID = t2.USER_ID
) t12,
(
    SELECT * FROM ANIME_DATASET_2023 t3 WHERE TYPE = 'TV'
) t3
WHERE t12.ANIME_ID = t3.ANIME_ID;

測試 SQL 2 (攤平的寫法)
#

我們直接把三張表攤平在最外層進行 Join，這是最標準的寫法。

1
2
3
4

SELECT *
FROM USERS_DETAILS_2023 t1, USERS_SCORE_2023 t2, ANIME_DATASET_2023 t3
WHERE t1.MAL_ID = t2.USER_ID AND t2.ANIME_ID = t3.ANIME_ID 
AND t3.TYPE = 'TV';

測試 SQL 3 關閉 Simple View Merging
# 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

ALTER SESSION SET "_simple_view_merging" = FALSE;
SELECT /*+ NO_MERGE */ *
FROM (
    SELECT t2.*
    FROM USERS_DETAILS_2023 t1, USERS_SCORE_2023 t2
    WHERE t1.MAL_ID = t2.USER_ID
) t12,
(
    SELECT * FROM ANIME_DATASET_2023 t3 WHERE TYPE = 'TV'
) t3
WHERE t12.ANIME_ID = t3.ANIME_ID;

執行計畫比較與觀察
#

SQL 1 (Inline View) 執行計畫：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                           PLAN TABLE OUTPUT                                           │
├───────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Plan hash value: 4024418878                                                                           │
│                                                                                                       │
│ ----------------------------------------------------------------------------------------------------- │
│ | Id  | Operation              | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | │
│ ----------------------------------------------------------------------------------------------------- │
│ |   0 | SELECT STATEMENT       |                    |  6028K|  7002M|       |   289K  (1)| 00:00:12 | │
│ |*  1 |  HASH JOIN             |                    |  6028K|  7002M|  4640K|   289K  (1)| 00:00:12 | │
│ |*  2 |   TABLE ACCESS FULL    | ANIME_DATASET_2023 |  4138 |  4586K|       |  1195   (1)| 00:00:01 | │
│ |*  3 |   HASH JOIN            |                    |    24M|  1925M|    12M|   178K  (1)| 00:00:07 | │
│ |   4 |    INDEX FAST FULL SCAN| SYS_C0010904       |   731K|  4284K|       |   389   (1)| 00:00:01 | │
│ |   5 |    TABLE ACCESS FULL   | USERS_SCORE_2023   |    24M|  1786M|       | 75469   (1)| 00:00:03 | │
│ ----------------------------------------------------------------------------------------------------- │
│                                                                                                       │
│ Predicate Information (identified by operation id):                                                   │
│ ---------------------------------------------------                                                   │
│                                                                                                       │
│ 1 - access("T2"."ANIME_ID"="T3"."ANIME_ID")                                                           │
│ 2 - filter("TYPE"=U'TV')                                                                              │
│ 3 - access("T1"."MAL_ID"="T2"."USER_ID")                                                              │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘

SQL 2 (攤平寫法) 執行計畫：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                         PLAN TABLE OUTPUT                                          │
├────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Plan hash value: 3836876227                                                                        │
│                                                                                                    │
│ -------------------------------------------------------------------------------------------------- │
│ | Id  | Operation           | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | │
│ -------------------------------------------------------------------------------------------------- │
│ |   0 | SELECT STATEMENT    |                    |  6028K|  7687M|       |   434K  (1)| 00:00:17 | │
│ |*  1 |  HASH JOIN          |                    |  6028K|  7687M|  4640K|   434K  (1)| 00:00:17 | │
│ |*  2 |   TABLE ACCESS FULL | ANIME_DATASET_2023 |  4138 |  4586K|       |  1195   (1)| 00:00:01 | │
│ |*  3 |   HASH JOIN         |                    |    24M|  4686M|    95M|   186K  (1)| 00:00:08 | │
│ |   4 |    TABLE ACCESS FULL| USERS_DETAILS_2023 |   731K|    87M|       |  3819   (1)| 00:00:01 | │
│ |   5 |    TABLE ACCESS FULL| USERS_SCORE_2023   |    24M|  1786M|       | 75469   (1)| 00:00:03 | │
│ -------------------------------------------------------------------------------------------------- │
│                                                                                                    │
│ Predicate Information (identified by operation id):                                                │
│ ---------------------------------------------------                                                │
│                                                                                                    │
│ 1 - access("T2"."ANIME_ID"="T3"."ANIME_ID")                                                        │
│ 2 - filter("T3"."TYPE"=U'TV')                                                                      │
│ 3 - access("T1"."MAL_ID"="T2"."USER_ID")                                                           │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘
  • SQL 3 關閉 Simple View Merging
 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: 3749485532                                                                            │
│                                                                                                        │
│ ------------------------------------------------------------------------------------------------------ │
│ | Id  | Operation               | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | │
│ ------------------------------------------------------------------------------------------------------ │
│ |   0 | SELECT STATEMENT        |                    |  6028K|    57G|       |  1113K  (1)| 00:00:44 | │
│ |*  1 |  HASH JOIN              |                    |  6028K|    57G|    37M|  1113K  (1)| 00:00:44 | │
│ |   2 |   VIEW                  |                    |  4138 |    37M|       |  1195   (1)| 00:00:01 | │
│ |*  3 |    TABLE ACCESS FULL    | ANIME_DATASET_2023 |  4138 |  4586K|       |  1195   (1)| 00:00:01 | │
│ |   4 |   VIEW                  |                    |    24M|    18G|       |   178K  (1)| 00:00:07 | │
│ |*  5 |    HASH JOIN            |                    |    24M|  1925M|    12M|   178K  (1)| 00:00:07 | │
│ |   6 |     INDEX FAST FULL SCAN| SYS_C0010904       |   731K|  4284K|       |   389   (1)| 00:00:01 | │
│ |   7 |     TABLE ACCESS FULL   | USERS_SCORE_2023   |    24M|  1786M|       | 75469   (1)| 00:00:03 | │
│ ------------------------------------------------------------------------------------------------------ │
│                                                                                                        │
│ Predicate Information (identified by operation id):                                                    │
│ ---------------------------------------------------                                                    │
│                                                                                                        │
│ 1 - access("T12"."ANIME_ID"="T3"."ANIME_ID")                                                           │
│ 3 - filter("TYPE"=U'TV')                                                                               │
└────────────────────────────────────────────────────────────────────────────────────────────────────────┘

觀察重點：

  1. 找不到 VIEW 關鍵字：雖然 SQL 1 寫了兩層厚厚的子查詢，但在它的執行計畫中，你完全找不到 VIEW 的操作步驟！Oracle 選擇直接把這兩個子查詢「合併 (Merge)」到外層，將它們轉換成與 SQL 2 幾乎一模一樣的 HASH JOIN 樹狀結構。這就是 Simple View Merging 的強大之處。
  2. Cost 差異的秘密：你可能會發現 SQL 1 的 Cost (289K) 竟然比 SQL 2 (434K) 還低？這其實是因為在 SQL 1 的子查詢中，我們特意只 SELECT t2.*。優化器發現它根本不需要 USERS_DETAILS_2023 (t1) 的其他實體欄位，只要拿 PK (MAL_ID) 去做關聯就好，於是聰明地對 t1 採用了極快的 INDEX FAST FULL SCAN（Cost 僅 389）。而 SQL 2 因為 SELECT * 拿了所有表的所有欄位，被迫必須對三張表都做全表掃描。
  3. 關閉 View Merging 的代價 (反向測試)：在我們故意加入 /*+ NO_MERGE */ 以及修改 Session 參數後，你可以在第三個執行計畫中清楚看到，多出了步驟 2 和 4 的 VIEW 操作。這代表子查詢被當作獨立、不可穿透的黑盒子處理。這不僅阻斷了優化器尋找最佳 Join 順序的可能，更導致 Cost 瘋狂飆升到了 1113K！這完美展示了為什麼優化器預設會竭盡所能地執行 Simple View Merging。

2. Complex View Merging 還是 View Formation？
#

當子查詢中包含了 GROUP BY 或 DISTINCT 時，因為涉及到資料的聚合，Oracle 的優化器會面臨兩種抉擇：

  1. Complex View Merging：把 GROUP BY 延後，先將 View 攤平與其他表進行 Join，最後再一起 GROUP BY。
  2. Group-By Placement (View Formation)：把 GROUP BY 提早執行，甚至主動幫你產生一個內部的 View，先聚合壓縮資料量後，再去跟其他表 Join。

讓我們看看以下測試。

測試 SQL 4 (先 GROUP BY 聚合，再 Join)
# 

1
2
3
4
5
6

SELECT t1.MAL_ID, t1.USERNAME, t2.counts
FROM USERS_DETAILS_2023 t1, 
     (SELECT USER_ID AS MAL_ID, count(USER_ID) AS counts 
      FROM USERS_SCORE_2023 t 
      GROUP BY t.USER_ID) t2
WHERE t1.MAL_ID = t2.MAL_ID;

測試 SQL 5 (先 Join，再 GROUP BY)
# 

1
2
3
4

SELECT t1.MAL_ID, t1.USERNAME, count(t2.USER_ID) counts
FROM USERS_DETAILS_2023 t1, USERS_SCORE_2023 t2
WHERE t1.MAL_ID = t2.USER_ID
GROUP bY t1.MAL_ID, t1.USERNAME, t1.rowid, t2.USER_ID;

測試 SQL 6 強制 MERGE
# 

1
2
3
4
5
6

SELECT t1.MAL_ID, t1.USERNAME, t2.counts
FROM USERS_DETAILS_2023 t1, 
     (SELECT /*+ MERGE */ USER_ID AS MAL_ID, count(USER_ID) AS counts 
      FROM USERS_SCORE_2023 t 
      GROUP BY t.USER_ID) t2
WHERE t1.MAL_ID = t2.MAL_ID;

執行計畫比較與觀察
#

SQL 4 (先聚合) 執行計畫：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                          PLAN TABLE OUTPUT                                          │
├─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Plan hash value: 3340517802                                                                         │
│                                                                                                     │
│ --------------------------------------------------------------------------------------------------- │
│ | Id  | Operation            | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | │
│ --------------------------------------------------------------------------------------------------- │
│ |   0 | SELECT STATEMENT     |                    |   274K|    13M|       |   109K  (1)| 00:00:05 | │
│ |*  1 |  HASH JOIN           |                    |   274K|    13M|     9M|   109K  (1)| 00:00:05 | │
│ |   2 |   VIEW               |                    |   274K|  6977K|       |   104K  (1)| 00:00:05 | │
│ |   3 |    HASH GROUP BY     |                    |   274K|  1610K|   279M|   104K  (1)| 00:00:05 | │
│ |   4 |     TABLE ACCESS FULL| USERS_SCORE_2023   |    24M|   139M|       | 75417   (1)| 00:00:03 | │
│ |   5 |   TABLE ACCESS FULL  | USERS_DETAILS_2023 |   731K|    18M|       |  3813   (1)| 00:00:01 | │
│ --------------------------------------------------------------------------------------------------- │
│                                                                                                     │
│ Predicate Information (identified by operation id):                                                 │
│ ---------------------------------------------------                                                 │
│                                                                                                     │
│ 1 - access("T1"."MAL_ID"="T2"."MAL_ID")                                                             │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘

SQL 5 (先 Join) 執行計畫：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                          PLAN TABLE OUTPUT                                          │
├─────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Plan hash value: 1113849487                                                                         │
│                                                                                                     │
│ --------------------------------------------------------------------------------------------------- │
│ | Id  | Operation            | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | │
│ --------------------------------------------------------------------------------------------------- │
│ |   0 | SELECT STATEMENT     |                    |   274K|    13M|       |   109K  (1)| 00:00:05 | │
│ |*  1 |  HASH JOIN           |                    |   274K|    13M|     9M|   109K  (1)| 00:00:05 | │
│ |   2 |   VIEW               | VW_GBF_5           |   274K|  6977K|       |   104K  (1)| 00:00:05 | │
│ |   3 |    HASH GROUP BY     |                    |   274K|  1610K|   279M|   104K  (1)| 00:00:05 | │
│ |   4 |     TABLE ACCESS FULL| USERS_SCORE_2023   |    24M|   139M|       | 75417   (1)| 00:00:03 | │
│ |   5 |   TABLE ACCESS FULL  | USERS_DETAILS_2023 |   731K|    18M|       |  3813   (1)| 00:00:01 | │
│ --------------------------------------------------------------------------------------------------- │
│                                                                                                     │
│ Predicate Information (identified by operation id):                                                 │
│ ---------------------------------------------------                                                 │
│                                                                                                     │
│ 1 - access("T1"."MAL_ID"="ITEM_1")                                                                  │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘
  • 測試 SQL 6 強制 MERGE
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

┌────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                         PLAN TABLE OUTPUT                                          │
├────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Plan hash value: 792262078                                                                         │
│                                                                                                    │
│ -------------------------------------------------------------------------------------------------- │
│ | Id  | Operation           | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | │
│ -------------------------------------------------------------------------------------------------- │
│ |   0 | SELECT STATEMENT    |                    |    24M|  1043M|       |   378K  (1)| 00:00:15 | │
│ |   1 |  HASH GROUP BY      |                    |    24M|  1043M|  1210M|   378K  (1)| 00:00:15 | │
│ |*  2 |   HASH JOIN         |                    |    24M|  1043M|    35M|   101K  (1)| 00:00:04 | │
│ |   3 |    TABLE ACCESS FULL| USERS_DETAILS_2023 |   731K|    27M|       |  3813   (1)| 00:00:01 | │
│ |   4 |    TABLE ACCESS FULL| USERS_SCORE_2023   |    24M|   139M|       | 75417   (1)| 00:00:03 | │
│ -------------------------------------------------------------------------------------------------- │
│                                                                                                    │
│ Predicate Information (identified by operation id):                                                │
│ ---------------------------------------------------                                                │
│                                                                                                    │
│ 2 - access("T1"."MAL_ID"="USER_ID")                                                                │
└────────────────────────────────────────────────────────────────────────────────────────────────────┘

觀察重點：

  1. 保留了 VIEW (拒絕 Merging)：在 SQL 4 中，優化器保留了 VIEW 這個步驟（步驟 2）。為什麼它不合併？因為 USERS_SCORE_2023 是一張高達 2400 萬筆紀錄的大表，優化器精算後發現：「先在裡面做 GROUP BY 把它壓縮成 27 萬筆，再去跟另一張表做 Hash Join」 的成本是最低的 (Cost 109K)。
  2. VW_GBF_5 (View Formation)：來看看最驚人的 SQL 5 執行計畫！我們明明語法寫的是「先將兩張表 Join 完，最後才在最外層 Group By」，但 Oracle 發現這是一條死路（先 Join 2400萬筆太慢了）。於是，優化器啟動了 Group-By Placement (聚合下推)，在底層「偷偷幫我們創建了一個名為 VW_GBF_5 的視圖 (View Formation)」！強制讓資料先做 Group By 壓縮，再丟出去 Join。
  3. 強制 MERGE 的災難 (測試 SQL 6)：我們在 SQL 6 故意加入了 /*+ MERGE */ Hint，強迫優化器無視一切把含有 GROUP BY 的子查詢給攤平。結果正如預期：VIEW 的關鍵字消失了，它被迫先拿高達 2400 萬筆的原始資料直接去跟 USERS_DETAILS_2023 做 HASH JOIN，最後才在最外層做 HASH GROUP BY。這導致 Cost 從原本的 109K 暴漲三倍多，來到了 378K！這完美證明了為什麼 CBO 一開始要理智地「拒絕」做 Complex View Merging。

結論
#

Oracle 的優化器不會盲目聽從 SQL 語法的字面層級：

  • 遇到單純的過濾與關聯，它會大刀闊斧地使用 Simple View Merging 將查詢攤平，以尋求最佳的 Join 順序。
  • 遇到千萬級別的 GROUP BY 與 Join 衝突時，它不但能判斷是否要保留 View，甚至還會主動幫那些「先 Join 再 Group By」的笨重寫法啟動 View Formation (VW_GBF_x)，提早下推壓縮資料量。

這再次驗證了：了解 Cost-Based Optimizer (CBO) 的查詢轉換 (Query Transformation) 邏輯，遠比死記硬背「子查詢到底會不會拖慢速度」的舊思維來得重要得多！