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

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月 1日 (木) / Author : Hiroshi Sekiguchi.

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

さあ, 今年もやってきましたよ. 12月の風物詩ITアドベントカレンダーの季節が loL. 今年は久々に, 復活

実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺

最後まで体力とネタが持つでしょうかww （いや, なんとしてもネタを持たせないと
帰ってきた！　実行計画は、SQL文のレントゲン写真だ！　Oracle Database (全部俺） Advent Calendar 2022

Day 1の窓は, 他のデータベースでも話題になっている標準SQLへの対応ネタから.
Oracle 19cまでは未対応だった, いくつかの集合演算子が, 20c以降でサポートされるようになりました.

簡単なとこからですよー!（最初から飛ばしすぎると後半息切れするのでw）

前述の集合演算子の拡張で特徴的だなと感じたのは, 今までALLがサポートされていなかった演算子でALLが使えるようになった事ですよね！

今回は, ALLがサポートされたINTERSECTの実行計画を見て見ましょう.

INTERSETのレントゲンは以前も紹介していました(Oracle 19c以前の実行計画)
https://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2019/12/post-9e4acc.html

今回のアドベントカレンダーでは, Oracle Database 21c Enterprise Edition を使います. (比較の為に, 旧リリースを使う場合もあります)

SCOTT@orclpdb1> select banner from v$version;

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

groupa表と, groupbh表を用意しました. num列だけの単純な表です（なお, 索引は作っていません. 理由は単純な集合演算を行いたかったから:)

SCOTT@orclpdb1> @day1
  1  SELECT
  2    num
  3  FROM
  4*   groupa

       NUM
----------
         1
         2
         2
         3
         4
         4
         5

7行が選択されました.

  1  SELECT
  2    num
  3  FROM
  4*   groupb

       NUM
----------
         0
         2
         3
         3
         4
         4
         4
         7

8行が選択されました.

上記のようなデータがあったとして, INTERSECT ALLするとどうなるか.

  1  SELECT
  2    num
  3  FROM
  4    groupa
  5  INTERSECT ALL
  6  SELECT
  7    num
  8  FROM
  9*   groupb

       NUM
----------
         2
         3
         4
         4

INTERSECTION ALL HASH と出てますね.　21cではこれを手がかりに, INTERSECT ALL行われていると判断できますね.

INTERSECTION ALL, このケースだとHASH操作を組み合わせてる. ふむむむ 21cだからですかね. 以前まではなかったはずなので, 後半で19cを利用して試してみましょう.

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

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     7 |    45 |     8  (25)| 00:00:01 |
|   1 |  INTERSECTION ALL HASH|        |       |       |            |          |
|   2 |   TABLE ACCESS FULL   | GROUPA |     7 |    21 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | GROUPB |     8 |    24 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

では, 以前のリリースからあったALL無しだとどうなるか. HASH操作も出てますね. 興味深い. 大量データを扱う場合は有利でしょうね.

  1  SELECT
  2    num
  3  FROM
  4    groupa
  5  IINTERSECT
  6  SELECT
  7    num
  8  FROM
  9*   groupb

       NUM
----------
         2
         3
         4

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

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |     7 |    45 |     8  (25)| 00:00:01 |
|   1 |  INTERSECTION HASH |        |       |       |            |          |
|   2 |   TABLE ACCESS FULL| GROUPA |     7 |    21 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| GROUPB |     8 |    24 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

では, HASHが選ばれない場合の簡単な例を.

表定義はこんな感じ. （削除フラグ！！！があるけど気にしないでね. 他のネタでも使いたいのでw）

名前                                    NULL?    型
----------------------------------------- -------- ----------------------------
UNIQUE_ID                                 NOT NULL NUMBER(10)
SUB_ITEM_CODE                             NOT NULL CHAR(10)
FOO                                       NOT NULL VARCHAR2(500)
IS_DELETE                                 NOT NULL NUMBER(1)

名前                                    NULL?    型
----------------------------------------- -------- ----------------------------
UNIQUE_ID                                 NOT NULL NUMBER(10)
SUB_ITEM_CODE                             NOT NULL CHAR(10)
FOO                                       NOT NULL VARCHAR2(500)
IS_DELETE                                 NOT NULL NUMBER(1)

索引関連はこんな感じ（索引スキャンしてもらうことを狙っています）

TABLE_NAME                     INDEX_NAME                     INDEX_TYPE                     UNIQUENESS
------------------------------ ------------------------------ ------------------------------ ---------------------------
TAB311                         TAB311_PK                      NORMAL                         UNIQUE

TAB311                         TAB311_IX_SUB_ITEM_CODE        NORMAL                         NONUNIQUE

TAB312                         IX01_TAB312                    NORMAL                         NONUNIQUE

TAB312                         IX02_TAB312                    NORMAL                         NONUNIQUE


TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
TAB311                         TAB311_IX_SUB_ITEM_CODE        SUB_ITEM_CODE

TAB311                         TAB311_PK                      UNIQUE_ID
TAB311                         TAB311_PK                      SUB_ITEM_CODE

TAB312                         IX01_TAB312                    UNIQUE_ID
TAB312                         IX02_TAB312                    SUB_ITEM_CODE

TAB312                         IX01_TAB312                    SUB_ITEM_CODE

以下のようなデータを用意しました.

  1  SELECT
  2    unique_id
  3  FROM
  4    tab311
  5  WHERE
  6*   unique_id BETWEEN 1 AND 50

 UNIQUE_ID
----------
         1
         1
         1
         2
         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
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44
        45
        46
        47
        48
        49
        50

53行が選択されました.


  1  SELECT
  2    unique_id
  3  FROM
  4    tab312
  5  WHERE
  6*   unique_id BETWEEN 25 AND 75

 UNIQUE_ID
----------
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44
        45
        46
        47
        48
        49
        50
        51
        52
        53
        54
        55
        56
        57
        58
        59
        60
        61
        62
        63
        64
        65
        66
        67
        68
        69
        70
        71
        72
        73
        74
        75

51行が選択されました.

INTERSECにしてみました. 索引が利用できる程度の取得件数なので索引を利用したSORT UNIQNE NOSORTが実行されています.
状況によっては, このようなケースもあることを思えておきましょうね. 索引利用が妥当なのかは個別の条件に応じて判断していく必要があります.
この場合は問題はないでしょう. ポイントはIndex only scanで必要最小限の範囲をアクセスして, かつ, 索引を利用してソートを回避しているところですね.

 1  SELECT
  2    unique_id
  3  FROM
  4    tab311
  5  WHERE
  6    unique_id BETWEEN 1 AND 50
  7  INTERSECT
  8  SELECT
  9    unique_id
 10  FROM
 11    tab312
 12  WHERE
 13*   unique_id BETWEEN 25 AND 75

 UNIQUE_ID
----------
        25
        26
        27
        28
        29
        30
        31
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44
        45
        46
        47
        48
        49
        50

26行が選択されました.


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

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |    50 |   612 |     8  (25)| 00:00:01 |
|   1 |  INTERSECTION       |             |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT|             |       |       |            |          |
|*  3 |    INDEX RANGE SCAN | TAB311_PK   |    50 |   300 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|             |       |       |            |          |
|*  5 |    INDEX RANGE SCAN | IX01_TAB312 |    52 |   312 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   3 - access("UNIQUE_ID">=1 AND "UNIQUE_ID"<=50)
   5 - access("UNIQUE_ID">=25 AND "UNIQUE_ID"<=75)

おまけ, 19cでは INTERSECT ALLはサポートされいませんが, 実行計画の違いを見ておきましょう.

SCOTT@ORCL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SCOTT@ORCL> @day1
  1  SELECT
  2    num
  3  FROM
  4*   groupa

       NUM
----------
         1
         2
         2
         3
         4
         4
         5

7 rows selected.


  1  SELECT
  2    num
  3  FROM
  4*   groupb

       NUM
----------
         0
         2
         3
         3
         4
         4
         4
         7

19cでは当然, シンタックスエラーです.

  1  SELECT
  2    num
  3  FROM
  4    groupa
  5  INTERSECT ALL
  6  SELECT
  7    num
  8  FROM
  9*   groupb
INTERSECT ALL
          *
ERROR at line 5:
ORA-00928: missing SELECT keyword

21cでは, HASH操作が行われてましたが, 19cでは, HASHの代わりに, SORT UNIQUEが現れていますね. 興味深い違いです. それぞれのリリースで同一統計情報で操作が変化する点は, しっかり押さえておきましょう. 今後のチューニングに役立つかもしれませんよー.

  1  SELECT
  2    num
  3  FROM
  4    groupa
  5  INTERSECT
  6  SELECT
  7    num
  8  FROM
  9*   groupb

       NUM
----------
         2
         3
         4


Execution Plan
----------------------------------------------------------
Plan hash value: 2012227029

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     7 |    45 |     8  (25)| 00:00:01 |
|   1 |  INTERSECTION       |        |       |       |            |          |
|   2 |   SORT UNIQUE       |        |     7 |    21 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| GROUPA |     7 |    21 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |        |     8 |    24 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| GROUPB |     8 |    24 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

参考）

19c UNION [ALL], INTERSECTおよびMINUS演算子

21c The Set Operators

ひとまず, Day 1の窓をあけた. 明日に続く.

最後に、今回は、ORDER BY句を付加していませんが、付けてくださいよ。ソートが必要な場合は！！！！

今回の例ではたまたま昇順に並んでいるだけですからね。

Related article on Mac De Oracle

投稿日時 2022年12月 1日 (木) 00時00分 Oracle, チューニング, SQL, パフォーマンス, Advent Calendar, 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月 1日 (木) / Author : Hiroshi Sekiguchi.

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

コメント

コメントを書く

Mac De Oracle

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー

Oracle

Apple

その他

Googleの広告

RSS

当サイトについて

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

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

コメント

コメントを書く

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