Mac De Oracle: 実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 22 / No.57 / Subquery Unnesting

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2024年9月
日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

最近のトラックバック

[memo]メモを取りつつ楽しめるようなスタイル (wmo6hash::blog)
ダーリンはGoogleで軽食なり。 (今日のゴハン)
�� プログラムは読む時間の方が長い - 遥か彼方の彼方から (�� プログラムは読む時間の方が長い - 遥か彼方の彼方から)
今流行のケブンリッジ関数・・・しってました！？ (あにずむ)
poken (drmarのメモ)
Photo exhibition "Natural Faces of AIDS Orphans"　写真展「エイズ孤児の素顔」@JICA地球ひろば　広尾 (メガ放談)
創る心とおもてなし―第1回 Webエンジニア料理対決に参加する (Kwappa開発室)
創る心とおもてなし～第一回webエンジニア料理対決に参加した (Mac De Oracle)
徹底比較!! PostgreSQL vs MySQLパート2 with 勝手に vs Firebird (14) (キムラデービーブログ)
Oracle de XMLDB #18 (discus)

Oracle

OTN US

Apple

その他

ブログ de ノベルティ
ノベルティグッズをただ、ただ、記録していくブログ。Mac De Oracleでは出来ないネタなので、別ブログを立てた次第。

Googleの広告

RSS

RSSを表示する

当サイトについて

プライバシーポリシー

■プライバシーポリシー

■個人情報の利用目的

　当ブログでは、メールでのお問い合わせ、ブログへのコメントの際、名前（ハンドルネーム）等の個人情報をご登録いただく場合がございます。これらの個人情報は質問に対する回答などでご連絡する場合や、スパムコメント抑止のために利用させていただくものであり、個人情報をご提供いただく際の目的以外では利用いたしません。

■個人情報の第三者への開示

　当サイトでは、個人情報は適切に管理し、以下に該当する場合を除いて第三者に開示することはありません。

本人のご了解がある場合
法令等への協力のため、開示が必要となる場合

個人情報の開示、訂正、追加、削除、利用停止については、ご本人からの個人データの開示、訂正、追加、削除、利用停止のご希望の場合には、ご本人であることを確認させていただいた上で速やかに対応させていただきます。

■アクセス解析ツールについて

　当サイトでは、Googleによるアクセス解析ツール「Googleアナリティクス」を利用しています。このGoogleアナリティクスはトラフィックデータの収集のためにCookieを使用しています。このトラフィックデータは匿名で収集されており、個人を特定するものではありません。この機能はCookieを無効にすることで収集を拒否することが出来ますので、お使いのブラウザの設定をご確認ください。

目的：アクセス数を把握するため
送信先：Google LCC
Googleは受け取った情報をどのように利用するか：Googleの規約に従います
送信される情報：以下４点(R), ユーザー数、セッションの統計情報、おおよその位置情報、ブラウザとデバイスの情報

■広告の配信について

当サイトはGoogleとAmazonの広告を配信しています。

Googleについて

Google及びGoogleのパートナー（第三者配信事業者）の提供する広告を設置しております。その広告配信にはCookieを使用し、当サイトやその他のサイトへの過去のアクセス情報に基づいて広告を配信します。

Google が広告 Cookie を使用することにより、当サイトや他のサイトにアクセスした際の情報に基づいて、Google やそのパートナーが適切な広告を表示しています。

お客様はGoogleアカウントの広告設定ページ（https://adssettings.google.com/u/0/authenticated）で、パーソナライズ広告を無効にできます。また aboutads.info のページにアクセスして頂き、パーソナライズ広告掲載に使用される第三者配信事業者のCookieを無効にできます。

その他、Googleの広告におけるCookieの取り扱い詳細については、Googleのポリシーと規約ページ（https://policies.google.com/technologies/ads）をご覧ください。

Amazonについて

AmazonはアソシエイトリンクのURLに含まれるパラメータを通じて利用者が直前に閲覧したアソシエイトサイトに関する情報などを収集し、これをアソシエイトパートナーに対する紹介料の算定、報告及び支払いに使用したり、アソシエイトパートナーによる複数のウェブサイトや商品戦略毎のパフォーマンスの分析に供したりしています。

アソシエイトパートナーの利用目的：閲覧の傾向や履歴の分析のため広告効果の分析のため
外部送信プログラム名：Amazonアソシエイトプログラム
送信先の会社名：アマゾンジャパン合同会社
送信する情報の内容：閲覧した内容についての情報送信先の利用目的：自社の広告宣伝を目的とした広告情報の掲載のため、ウェブサイトやアプリの監視のため

Amazonによる個人に関する情報の取得及び取扱いに関する方針につきましてはAmazon.co.jpプライバシー規約をご覧ください。

■免責事項

　当サイトからリンクやバナーなどによって他のサイトに移動された場合、移動先サイトで提供される情報、サービス等について一切の責任を負いません。当サイトのコンテンツ・情報につきまして、可能な限り正確な情報を掲載するよう努めておりますが、誤情報が入り込んだり、情報が古くなっていることもございます。当サイトに掲載された内容によって生じた損害等の一切の責任を負いかねますのでご了承ください。

■プライバシーポリシーの変更について

　当サイトは、個人情報に関して適用される日本の法令を遵守するとともに、本ポリシーの内容を適宜見直しその改善に努めます。
修正された最新のプライバシーポリシーは常に本ページにて開示されます。

2022年12月22日 (木) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 22 / No.57 / Subquery Unnesting Tweet

Previously on Mac De Oracle...

Day 21は, INLIST ITERATOR と Sub Query と STATISTICS COLLECTORを取り上げました. 古くからあるOperationを最適化する比較的新しい機能ですね. とはいえ, 現場で見たことない！　なんていうのは多そうではあります.

では, Day 22の窓を開けましょう！

昨日の記事でちょっとだけ話題にした, Subquery Unnesting なので, Subquery Unnestingの代表的な3例を診ておきましょう.

いつもと同じように 21c で確認します.

SCOTT@orclpdb1> select banner from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

まず, これも通常は, SQLを書き換えないと対応タイプですが, Oracleは結合に書き換えてくれます. 大量にデータを返すのに, スカラー副問い合わせが使われていて辛かった経験って一度ぐらいはありそうですね.
（私も過去一度だけこいつのチューニングしたことがありますが, 当時はスカラー副問い合わせをUnnestingできなかったので, SQLをOUTER JOINに書き換えてもらいました！）

以下の例では, スカラー副問い合わせが, OUTER JOINかつ, このケースではMERGE JOINに書き換えられています.

SCOTT@orclpdb1> @day22
  1  SELECT
  2    deptno
  3    ,dname
  4    ,(
  5       SELECT
  6          MAX(sal)
  7       FROM
  8          emp
  9       WHERE
 10          emp.deptno=dept.deptno
 11    ) AS max_sal
 12  FROM
 13    dept
 14  ORDER BY
 15*   deptno

    DEPTNO DNAME                                         MAX_SAL
---------- ------------------------------------------ ----------
        10 ACCOUNTING                                       5000
        20 RESEARCH                                         3000
        30 SALES                                            2850
        40 OPERATIONS

実行計画
----------------------------------------------------------
Plan hash value: 2834279049

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     4 |   116 |     7  (29)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |          |     4 |   116 |     7  (29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT  |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |          |     3 |    48 |     5  (40)| 00:00:01 |
|   5 |    VIEW                      | VW_SSQ_1 |     3 |    48 |     4  (25)| 00:00:01 |
|   6 |     HASH GROUP BY            |          |     3 |    21 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL       | EMP      |    12 |    84 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   4 - access("ITEM_1"(+)="DEPT"."DEPTNO")
       filter("ITEM_1"(+)="DEPT"."DEPTNO")

では, Scalar Subquery Unnestingされなかった場合はどう見えるでしょうか？（これが, SELECTリストで利用されたScalar subqueryの実行計画です. NO_UNNESTヒントで抑止します.
スカラーサブクエリーが実行計画の最初に現れるのが特徴です（Oracleの場合）

以下の例では, dept表の行数分, Nested Loop Joinのように, emp表へのアクセスが繰り返し実行されます. なので, 行数が多い場合は, HASH JOINなどに書き換えた方が効率が良いわけです. Scalar Subquery UnnestingはそれをOracleが内部的に実施してくれている便利な機能なんですよ.

  1  SELECT
  2    deptno
  3    ,dname
  4    ,(
  5       SELECT
  6          /*+
  7            NO_UNNEST
  8          */
  9          MAX(sal)
 10       FROM
 11          emp
 12       WHERE
 13          emp.deptno=dept.deptno
 14    ) AS max_sal
 15  FROM
 16    dept
 17  ORDER BY
 18*   deptno

    DEPTNO DNAME                                         MAX_SAL
---------- ------------------------------------------ ----------
        10 ACCOUNTING                                       5000
        20 RESEARCH                                         3000
        30 SALES                                            2850
        40 OPERATIONS

実行計画
----------------------------------------------------------
Plan hash value: 1283604845

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |     4 |    52 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |         |     1 |     7 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |     4 |    28 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX_EMP  |     4 |       |     1   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID         | DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |   INDEX FULL SCAN                    | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   3 - access("EMP"."DEPTNO"=:B1)

次は, IN条件内のサブクエリーがUnnestingされた例です.
相関関係のないIN副問合せや, IN(集計ファンクションまたはGROUP BY句を含まない場合)に書き換えるとされています. この例は, 相関関係のない副問合せにしてあります. IN LIST ITERATION等ではなく, SubqueryがUnnestingされ, Nested Loop Joinに書き換えられています！

  1  SELECT
  2    deptno
  3    ,dname
  4  FROM
  5    dept
  6  WHERE
  7    deptno IN (
  8      SELECT
  9        deptno
 10      FROM
 11        emp
 12      WHERE
 13        sal < 1000
 14    )
 15  ORDER BY
 16*   deptno

    DEPTNO DNAME
---------- ------------------------------------------
        20 RESEARCH
        30 SALES

実行計画
----------------------------------------------------------
Plan hash value: 35786210

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    20 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                  |         |     1 |    20 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS                 |         |     1 |    20 |     4  (25)| 00:00:01 |
|   3 |    SORT UNIQUE                 |         |     1 |     7 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| EMP     |     1 |     7 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | IX_EMP  |    12 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN           | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   4 - filter("SAL"<1000)
   6 - access("DEPTNO"="DEPTNO")

IN条件のSubqueryのUnnestingをNO_UNNESTヒントで抑止すると元の実行計画という名のレントゲンが現れてきます.
Unnestingが抑止されたことで, 結合ではなくFILTER条件(EXISTS書き換えられ)になっていることがわかりますよね. どちらの実行計画が有利にかは状況次第ですが, 以前も書いたように, 最近の傾向では, 積極的にUnnestingしているように感じています.

  1  SELECT
  2    deptno
  3    ,dname
  4  FROM
  5    dept
  6  WHERE
  7    deptno IN (
  8      SELECT
  9        /*+
 10          NO_UNNEST
 11        */
 12        deptno
 13      FROM
 14        emp
 15      WHERE
 16        sal < 1000
 17    )
 18  ORDER BY
 19*   deptno

    DEPTNO DNAME
---------- ------------------------------------------
        20 RESEARCH
        30 SALES

実行計画
----------------------------------------------------------
Plan hash value: 3458873336

-------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |     1 |    13 |     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID          | DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                     | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |     1 |     7 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IX_EMP  |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE "DEPTNO"=:B1 AND
              "SAL"<1000))
   3 - filter("SAL"<1000)
   4 - access("DEPTNO"=:B1)

最後は, EXISTS演算子内のサブクエリーがUnnestingされた例ですよー. こちらも同じようにUnnestingは積極的に行われているように思います. 私が関わった範囲ですけども.
なお, 書き換え条件として, EXISTS相関副問合せ(集計ファンクションまたはGROUP BY句を含まない場合)となっています. 以下の例でも, 集計ファンクション, GROUP BYを含まない相関服問い合わせにしてあります.

  1  SELECT
  2    deptno
  3    ,dname
  4  FROM
  5    dept
  6  WHERE
  7    EXISTS (
  8      SELECT
  9        1
 10      FROM
 11        emp
 12      WHERE
 13        sal < 1000
 14        AND emp.deptno = dept.deptno
 15    )
 16  ORDER BY
 17*   deptno

    DEPTNO DNAME
---------- ------------------------------------------
        20 RESEARCH
        30 SALES

実行計画
----------------------------------------------------------
Plan hash value: 35786210

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |    20 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                  |         |     1 |    20 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS                 |         |     1 |    20 |     4  (25)| 00:00:01 |
|   3 |    SORT UNIQUE                 |         |     1 |     7 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| EMP     |     1 |     7 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | IX_EMP  |    12 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN           | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   4 - filter("SAL"<1000)
   6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

NO_UNNESTヒントでUnnestingを抑止してもとの実行計画を覗いています！
Plan hash valueを見ると, なんと, IN条件のNO_UNNESTヒントを付加した実行計画と同じに！

  1  SELECT
  2    deptno
  3    ,dname
  4  FROM
  5    dept
  6  WHERE
  7    EXISTS (
  8      SELECT
  9        /*+
 10          NO_UNNEST
 11        */
 12        1
 13      FROM
 14        emp
 15      WHERE
 16        sal < 1000
 17        AND emp.deptno = dept.deptno
 18    )
 19  ORDER BY
 20*   deptno

    DEPTNO DNAME
---------- ------------------------------------------
        20 RESEARCH
        30 SALES

実行計画
----------------------------------------------------------
Plan hash value: 3458873336

-------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |         |     1 |    13 |     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID          | DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN                     | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |     1 |     7 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IX_EMP  |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "EMP" WHERE
              "EMP"."DEPTNO"=:B1 AND "SAL"<1000))
   3 - filter("SAL"<1000)
   4 - access("EMP"."DEPTNO"=:B1)

ということで, 残り3日まで窓を開けました-　；）

安心して, 寝坊しないようにしないと.

ではまた.

参考）
Oracle Database 21c SQL Language Reference / Unnesting of Nested Subqueries

投稿日時 2022年12月22日 (木) 00時00分 Oracle, Oracle Database 12c release 1, Advent Calendar, Oracle Database 12c Release 2, Oracle Database 18c, Oracle Database 19c, Oracle Database 21c | 固定リンク | 0

Mac De Oracle

MacOS X で動作する Oracle　が正式にリリースされたのだ！ 1996年にリリースされた、Personal Oracle7 for MacOS以来、封印されていたMacOS版が、（2005年）９年ぶりに正式リリースされたのでいろいろあそんでみたくなった！

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

2022年12月22日 (木) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 22 / No.57 / Subquery Unnesting Tweet

コメント

コメントを書く

Mac De Oracle

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

2022年12月22日 (木) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 22 / No.57 / Subquery Unnesting Tweet

コメント

コメントを書く

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 22 / No.57 / Subquery Unnesting Tweet