GAGA LIFE.

DBAブログ

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;

------------------------------------------------------------------------------------------------------------------
| 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
https://docs.oracle.com/cd/E96517_01/tgsql/index.html
Oracle(R) Database SQLチューニング・ガイド 18c