GAGA LIFE.

DBAブログ

Oracle 問合せ変換③ 述語のプッシュ(Predicate Push Down)

概要

述語のプッシュ(またはプッシュダウン)は、前回記事で説明したビュー・マージが不可能なビューに対する問合せであった場合に、主問合せの述語をビュー定義の中に組み入れる(プッシュ)ことによって、実行の初期段階で多くの行を絞り込むことが期待できます。
そのことで、以下のような効果があります。
  ・索引アクセスが選択可能になる
  ・次のオペレーション(ソート等)で処理するデータ量が少なくなり効率的になる

述語のプッシュは、以下の通り、2つのサブカテゴリがあります。

・フィルタプッシュダウン(Filter Push Down):マージできないビューまたはインラインビュー内で制限(フィルタリング条件)をプッシュすること
・結合述語プッシュダウン(Join Predicate Push Down):マージできないビューまたはインライン・ビューの中で結合条件をプッシュすること

それぞれ、フィルタプッシュダウンはヒューリスティックベース、結合述語プッシュダウンはコストベースのクエリ変換です。

変換イメージ

--Filter Push Down

SELECT *
  FROM
      (SELECT * FROM t1
             UNION
       SELECT * FROM t2
      )
WHERE id = 1

↓↓↓変換↓↓↓

SELECT *
  FROM
     (SELECT * FROM t1
        WHERE id = 1
           UNION
      SELECT * FROM t2
        WHERE id = 1
     )

上記例では、インラインビュー内の制限(id = 1)をプッシュしています。 このことで以下の効果が得られます。
・2つのテーブルにインデックスを介してアクセスできる。
・UNIONセット演算子に必要な並べ替え操作ができるだけ少ない行で処理される

--Join Predicate Push Down

SELECT *
  FROM t1,
        (SELECT * FROM t2
               UNION
         SELECT * FROM t3
        ) t23
WHERE t1.id = t23.id

↓↓↓変換↓↓↓
SELECT *
  FROM t1,
        (SELECT * FROM t2
           WHERE t2.id = t1.id
              UNION
         SELECT * FROM t3
           WHERE t3.id = t1.id
        ) t23

上記例では、結合条件(t1.id = t23.id)をインライン・ビュー内にプッシュしています。
この変換によって得られる利点は、フィルタプッシュダウンに記載したのと同じ利点(インデックスアクセスを有効にし、ソートするデータ量を削減)です。
この特定のケースでは、追加のアクセス・パスにより、問合せオプティマイザは、使用可能なすべての「結合メソッド」と「結合順序」を自由に選択できるようになります。

実行例

下記は、結合述語プッシュダウン(JPPD)変換の例です。

--Join Predicate Push Down

WITH agg_q
         AS ( SELECT s.cust_id
                            ,prod_id
                            ,SUM (s.amount_sold) total_amt_sold
                   FROM sh.sales s JOIN sh.products p USING (prod_id)
          GROUP BY s.cust_id,prod_id)
SELECT cust_id
             ,c.cust_first_name
             ,c.cust_last_name
             ,c.cust_email
             ,agg_q.total_amt_sold
   FROM agg_q RIGHT JOIN sh.customers c USING (cust_id)
WHERE cust_first_name = 'Abner' AND cust_last_name = 'Everett';

Plan hash value: 273701464
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                  |                |     1 |    63 |   478   (1)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER                               |                |     1 |    63 |   478   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL                               | CUSTOMERS      |     1 |    48 |   423   (1)| 00:00:01 |
|   3 |    VIEW PUSHED PREDICATE                          |                |     1 |    15 |    56   (2)| 00:00:01 |
|   4 |     SORT GROUP BY                                 |                |    31 |   806 |    56   (2)| 00:00:01 |
|   5 |      NESTED LOOPS                                 |                |    43 |  1118 |    56   (2)| 00:00:01 |
|   6 |       VIEW                                        | VW_GBC_6       |    43 |   946 |    56   (2)| 00:00:01 |
|   7 |        SORT GROUP BY                              |                |    43 |   602 |    56   (2)| 00:00:01 |
|   8 |         PARTITION RANGE ALL                       |                |   130 |  1
820 |    55   (0)| 00:00:01 |
|   9 |          TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |   130 |  1820 |    55   (0)| 00:00:01 |
|  10 |           BITMAP CONVERSION TO ROWIDS             |                |       |       |            |          |
|* 11 |            BITMAP INDEX SINGLE VALUE              | SALES_CUST_BIX |       |       |            |          |
|* 12 |      INDEX UNIQUE SCAN                            | PRODUCTS_PK    |     1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C"."CUST_FIRST_NAME"='Abner' AND "C"."CUST_LAST_NAME"='Everett')
  11 - access("S"."CUST_ID"="C"."CUST_ID")
  12 - access("ITEM_1"="P"."PROD_ID")

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

WITH agg_q
         AS ( SELECT /*+ no_push_pred */
                            s.cust_id
                           ,prod_id
                           ,SUM (s.amount_sold) total_amt_sold
                   FROM sh.sales s JOIN sh.products p USING (prod_id)
           GROUP BY s.cust_id,prod_id)
SELECT cust_id
             ,c.cust_first_name
             ,c.cust_last_name
             ,c.cust_email
             ,agg_q.total_amt_sold
   FROM agg_q RIGHT JOIN sh.customers c USING (cust_id)
 WHERE cust_first_name = 'Abner' AND cust_last_name = 'Everett';

Plan hash value: 1957717102
-------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name       
| Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |    2 |   148 |       |  6358   (1)| 00:00:01 |
|*  1 |  HASH JOIN OUTER           |             |    2 |   148 |       |  6358   (1)| 00:00:01 |
|   2 |   JOIN FILTER CREATE       |     :BF0000 |    1 |    48 |       |   423   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL       |   CUSTOMERS |    1 |    48 |       |   423   (1)| 00:00:01 |
|   4 |   VIEW                     |             |  359K|  9125K|       |  5934   (1)| 00:00:01 |
|   5 |    HASH GROUP BY           |             |  359K|  9125K|    12M|  5934   (1)| 00:00:01 |
|   6 |     JOIN FILTER USE        |     :BF0000 |  359K|  9125K|       |  3275   (2)| 00:00:01 |
|*  7 |      HASH JOIN             |             |  359K|  9125K|       |  3275   (2)| 00:00:01 |
|   8 |       INDEX FULL SCAN      | PRODUCTS_PK |   72 |   288 |       |     1   (0)| 00:00:01 |
|   9 |       VIEW                 |    VW_GBC_6 |  359K|  7721K|       |  3273   (2)| 00:00:01 |
|  10 |        HASH GROUP BY       |             |  359K|  4913K|    21M|  3273   (2)| 00:00:01 |
|  11 |         PARTITION RANGE ALL|             |  918K|    12M|       |   517   (2)| 00:00:01 |
|  12 |          TABLE ACCESS FULL |       SALES |  918K|    12M|       |   517   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - access("C"."CUST_ID"="AGG_Q"."CUST_ID"(+))
  3 - filter("C"."CUST_FIRST_NAME"='Abner' AND "C"."CUST_LAST_NAME"='Everett')
  7 - access("ITEM_1"="P"."PROD_ID")

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

SELECT
         "C"."CUST_ID" "CUST_ID",
         "C"."CUST_FIRST_NAME" "CUST_FIRST_NAME",
         "C"."CUST_LAST_NAME" "CUST_LAST_NAME",
         "C"."CUST_EMAIL" "CUST_EMAIL",
         "AGG_Q"."TOTAL_AMT_SOLD" "TOTAL_AMT_SOLD"
FROM
         "SH"."CUSTOMERS" "C",
         (
               SELECT
                      "VW_GBC_6"."ITEM_3" "CUST_ID",
                      "P"."PROD_ID" "PROD_ID",
                      SUM("VW_GBC_6"."ITEM_2") "TOTAL_AMT_SOLD"
               FROM
                      (
                          SELECT
                                   "S"."PROD_ID" "ITEM_1",
                                   SUM("S"."AMOUNT_SOLD") "ITEM_2",
                                   "S"."CUST_ID" "ITEM_3"
                          FROM
                                    "SH"."SALES" "S"
                          WHERE
                                    "S"."CUST_ID" = "C"."CUST_ID"
                          GROUP BY
                                    "S"."PROD_ID",
                                    "S"."CUST_ID"
                      ) "VW_GBC_6",
                      "SH"."PRODUCTS" "P"
               WHERE
                      "VW_GBC_6"."ITEM_1" = "P"."PROD_ID"
               GROUP BY
                      "VW_GBC_6"."ITEM_3",
                      "P"."PROD_ID"
         ) "AGG_Q"
WHERE
        "C"."CUST_FIRST_NAME" = 'Abner'
AND "C"."CUST_LAST_NAME" = 'Everett'

Appendix

ヒューリスティックベースのクエリ変換:特定の条件が満たされたときに適用される。ほとんどの状況でより良い実行計画につながると期待されている。
コストベースのクエリ変換:コスト見積もりによって計算されたコストに応じて、元のステートメントよりもコストが低い実行計画につながる場合に適用される

参考資料

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