Mac De Oracle: 実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その2

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2026年6月
日	月	火	水	木	金	土
	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の広告を配信しています。

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）をご覧ください。

■免責事項

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

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

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

2024年7月23日 (火) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その2 Tweet

Previously on Mac De Oracle
実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その1では、参照整合性制約を利用した Join Elimination の挙動を確認しました。

PostgreSQL、意外にも（知ってたくせに〜w）行われませんでしたね　MySQLは事前の想定通りでしたが:)

では、次の Join Elimination のテストケースを確認してみましょう。

シンプルな例で試しています。Oracle Database / PostgreSQL / MySQL それぞれに以下の2表を作成しておきます。
どちらの表も id 列が主キーですが、前回のケースのような参照整合性制約はありません。

Oracle Database同様の表をMySQL/PostgreSQLにも作成して検証。（データはあってもなくても結合の除外には影響しないためデータは登録していません）
(なおデータ型は、MySQL/PostgreSQLに合わせて変更しています。e.g. NUMBER->INTEGER, VARCHAR2 -> VARCHAR。　また、MySQL/PostgreSQLそれぞれで統計情報も取得しておきます）

SCOTT@orclpdb1> CREATE TABLE foo (id NUMBER PRIMARY KEY, note VARCHAR2(100));

表が作成されました。

SCOTT@orclpdb1> CREATE TABLE bar (id NUMBER PRIMARY KEY, memo VARCHAR2(100));

表が作成されました。

SCOTT@orclpdb1> !cat gather_tab_stats.sql
set verify on
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>upper('&1'),cascade=>true,no_invalidate=>false);
set verify off
undefine 1

SCOTT@orclpdb1> @gather_tab_stats foo

PL/SQLプロシージャが正常に完了しました。

SCOTT@orclpdb1> @gather_tab_stats bar

PL/SQLプロシージャが正常に完了しました。

前述の表を使い、以下のSQL文を実行します！
このSQL文では、bar表を外部結合していますが、SELECTリストでは bar表を参照していません。
また、foo.id = bar.id は 1 : 0..1 であるため、bar表の対象行の結合されるかどうかは問合せ結果に影響しないようにしてあります。つまり、bar表は結合しなくてもよい問合せにしてあります。。。
（さあ、どうなるでしょうね。楽しくなってきました）

SELECT
  foo.id
  , foo.note
FROM
  foo
  LEFT OUTER JOIN bar
  ON
    foo.id = bar.id;

Oracle Database (21c)
すばらしい。無駄な結合を見つけ、Join Elimination していることを確認できます。（分かってましたけどw）

SCOTT@orclpdb1> l
  1  EXPLAIN PLAN FOR
  2  SELECT
  3    foo.id
  4    , foo.note
  5  FROM
  6    foo
  7    LEFT OUTER JOIN bar
  8    ON
  9*     foo.id = bar.id
 SCOTT@orclpdb1> /

解析されました。

経過: 00:00:00.01
SCOTT@orclpdb1> @?/rdbms/admin/utlxpls 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1245013993

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    65 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| FOO  |     1 |    65 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8行が選択されました。

PostgreSQL (16.3)
おおおおおおおーーーーーーーっ。このケースでは、PostgreSQLも Join Elimination しています！！！！！
bar表が結合されていません！　これまた新しい気づき。メモメモw

PostgreSQLのプランナー。 Join Elimination を全く実装していないのかと思いましたが、限定的なようですが、 Join Elimination が実装されているように見えますよね。
興味深い。発展途上というところか。。（次回のPostgreSQL アンカンファレンスで聞いてみようかな）

perftestdb=> EXPLAIN verbose
perftestdb-> SELECT
perftestdb->   foo.id
perftestdb->   , foo.note
perftestdb-> FROM
perftestdb->   foo
perftestdb->   LEFT OUTER JOIN bar
perftestdb->   ON
perftestdb->     foo.id = bar.id;

                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on public.foo  (cost=0.00..0.00 rows=1 width=222)
   Output: foo.id, foo.note
(2 行)

MySQL (8.0.36)
んーーーーっ。MySQLのオプティマイザーは、Join Elimination は考慮していないように見えますよ。軽量なオプティマイザーが売りだからだろうか。。

mysql> EXPLAIN format=tree
    -> SELECT
    ->   foo.id
    ->   , foo.note
    -> FROM
    ->   foo
    ->   LEFT OUTER JOIN bar
    ->   ON
    ->     foo.id = bar.id;
+-----------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                       |
+-----------------------------------------------------------------------------------------------+
| -> Nested loop left join  (cost=0.7 rows=1)
    -> Table scan on foo  (cost=0.35 rows=1)
    -> Single-row covering index lookup on bar using PRIMARY (id=foo.id)  (cost=0.35 rows=1)
 |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

海外のブログでは、実装されてない！　と言い切られているのもありましたが、PostgreSQL 16 では限定的ですが行われるようですね。（PostgreSQL 16より前のリリースってどうなんだろう。。13のままにしてたほうがおもしろかったかな。。。むむむ

ということで、次回へつづく。

投稿日時 2024年7月23日 (火) 12時00分 Oracle, チューニング, FAQ, SQL, MySQL, PostgreSQL, パフォーマンス | 固定リンク | 0

Mac De Oracle

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

DROP SHADOW CTRL

GaragebandとiMovieで

検索

Twitter

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー