概要

述語のプッシュ(またはプッシュダウン)は、前回記事で説明したビュー･マージが不可能なビューに対する問合せであった場合に、主問合せの述語をビュー定義の中に組み入れる(プッシュ)ことによって、実行の初期段階で多くの行を絞り込むことが期待できます。
そのことで、以下のような効果があります。
- 索引アクセスが選択可能になる
- 次のオペレーション(ソート等)で処理するデータ量が少なくなり効率的になる
述語のプッシュは、以下の通り、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     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   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 |  1820 |    55   (0)| 00:00:01 |     1 |    28 |
|   9 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES          |   130 |  1820 |    55   (0)| 00:00:01 |     1 |    28 |
|  10 |          BITMAP CONVERSION TO ROWIDS             |                |       |       |            |          |       |       |
|* 11 |           BITMAP INDEX SINGLE VALUE              | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|* 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     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   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 |     1 |    28 |
|  12 |          TABLE ACCESS FULL | SALES       |   918K|    12M|       |   517   (2)| 00:00:01 |     1 |    28 |
------------------------------------------------------------------------------------------------------------------

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

Expert Oracle SQL: Optimization, Deployment, and Statistics

作者: Tony Hasler
出版社/メーカー: Apress
発売日: 2014/06/24
メディア: ペーパーバック
この商品を含むブログを見る

Troubleshooting Oracle Performance, Second Edition

Troubleshooting Oracle Performance

作者: Christian Antognini
出版社/メーカー: Apress
発売日: 2014/06/02
メディア: ペーパーバック
この商品を含むブログを見る

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

GAGA LIFE.

インフラエンジニアブログ

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

概要

変換イメージ

Filter Push Down

Join Predicate Push Down

実行例

Join Predicate Push Down

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

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

Appendix

参考資料