Oracle 問合せ変換②-1 ビュー・マージ(Simple View Merging)

概要

Oracleの問合せ変換の中の「ビュー・マージ(View Merging)」について記載します。
今回の実行例は、Simple View Merging編です。
- Simple View Merging:単純な結合のクエリブロックをマージするために使用される
- Complex View Merging:集計を含むクエリブロックをマージするために使用される要は、GROUP BYもしくはDISTINCTを含むビューがマージされる

目的

SQL内でビュー(インライン・ビューを含む)と実表とを結合するような場合、はじめにビューへの問合せが実行されます。
しかしながら、それが非効率でパフォーマンスが出ない(※)場合があります。
※例:結合順序が悪い、元表のインデックスが使用できない

上記のような場合に、ビューに対する問合せをビューの元表に直接問い合わせるSELECT文に変換し、
そのSELECT文をユーザがビューに対して実行した問合せ内に組み入れる(マージ)ことで
オプティマイザが選択可能な実行計画の幅を拡げます。
例えば、ビューを元表へのSELECT文に変換したことで、ビュー→テーブルという結合順序を
テーブル→ビュー(SELECT変換後)のようにしたり、元表の索引が使用できるようになります。

実行例

Simple view merging

WITH q1
     AS (SELECT 
               CASE prod_category
                   WHEN 'Electronics' THEN amount_sold * 0.9
                   ELSE amount_sold
                END
                   AS adjusted_amount_sold
           FROM sh.sales JOIN sh.products USING (prod_id))
  SELECT adjusted_amount_sold, COUNT (*) cnt
    FROM q1
GROUP BY adjusted_amount_sold;

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                      | 12679 |   371K|       |  3237   (2)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY           |                      | 12679 |   371K|    35M|  3237   (2)| 00:00:01 |       |       |
|*  2 |   HASH JOIN              |                      |   918K|    26M|       |   522   (2)| 00:00:01 |       |       |
|   3 |    VIEW                  | index$_join$_002     |    72 |  1512 |       |     2   (0)| 00:00:01 |       |       |
|*  4 |     HASH JOIN            |                      |       |       |       |            |          |       |       |
|   5 |      INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   6 |      INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |       |     1   (0)| 00:00:01 |       |       |
|   7 |    PARTITION RANGE ALL   |                      |   918K|  8075K|       |   517   (2)| 00:00:01 |     1 |    28 |
|   8 |     TABLE ACCESS FULL    | SALES                |   918K|  8075K|       |   517   (2)| 00:00:01 |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
   4 - access(ROWID=ROWID)

変換されていない実行計画(NO_MERGE)

WITH q1
     AS (SELECT /*+ NO_MERGE */
               CASE prod_category
                   WHEN 'Electronics' THEN amount_sold * 0.9
                   ELSE amount_sold
                END
                   AS adjusted_amount_sold
           FROM sh.sales JOIN sh.products USING (prod_id))
  SELECT adjusted_amount_sold, COUNT (*) cnt
    FROM q1
GROUP BY adjusted_amount_sold;

Plan hash value: 2089506595

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                      |   918K|    11M|   546   (7)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY            |                      |   918K|    11M|   546   (7)| 00:00:01 |       |       |
|   2 |   VIEW                    |                      |   918K|    11M|   522   (2)| 00:00:01 |       |       |
|*  3 |    HASH JOIN              |                      |   918K|    26M|   522   (2)| 00:00:01 |       |       |
|   4 |     VIEW                  | index$_join$_002     |    72 |  1512 |     2   (0)| 00:00:01 |       |       |
|*  5 |      HASH JOIN            |                      |       |       |            |          |       |       |
|   6 |       INDEX FAST FULL SCAN| PRODUCTS_PK          |    72 |  1512 |     1   (0)| 00:00:01 |       |       |
|   7 |       INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX |    72 |  1512 |     1   (0)| 00:00:01 |       |       |
|   8 |     PARTITION RANGE ALL   |                      |   918K|  8075K|   517   (2)| 00:00:01 |     1 |    28 |
|   9 |      TABLE ACCESS FULL    | SALES                |   918K|  8075K|   517   (2)| 00:00:01 |     1 |    28 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")
   5 - access(ROWID=ROWID)

最適化後のSQL(10053トレース)

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT
    CASE "PRODUCTS"."PROD_CATEGORY"
        WHEN 'Electronics' THEN "SALES"."AMOUNT_SOLD" * 0.9
        ELSE "SALES"."AMOUNT_SOLD"
    END "ADJUSTED_AMOUNT_SOLD",
    COUNT(*) "CNT"
FROM
    "SH"."SALES" "SALES",
    "SH"."PRODUCTS" "PRODUCTS"
WHERE
    "SALES"."PROD_ID" = "PRODUCTS"."PROD_ID"
GROUP BY
    CASE "PRODUCTS"."PROD_CATEGORY"
        WHEN 'Electronics' THEN "SALES"."AMOUNT_SOLD" * 0.9
        ELSE "SALES"."AMOUNT_SOLD"
    END

関連パラメータ

optimizer_secure_view_merging

参考資料

Expert Oracle SQL

Expert Oracle SQL: Optimization, Deployment, and Statistics

https://docs.oracle.com/cd/E96517_01/tgsql/index.html
Oracle(R) Database SQLチューニング・ガイド 18c