Mac De Oracle: 実行計画は, SQL文のレントゲン写真だ！ No.64 / 先生、私のLEFT OUTER JOINが無いんです！！（Join Elimination番外編）

DROP SHADOW CTRL

TEXT DROP SHADOW OFF

GaragebandとiMovieで

検索

検索用語を入力

Web Blog内

検索フォームを送信

プロフィール

Twitter

Tweets by @discus_hamburg

2024年10月
日	月	火	水	木	金	土
		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	31

最近のトラックバック

[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プライバシー規約をご覧ください。

■免責事項

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

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

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

2024年8月21日 (水) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ！ No.64 / 先生、私のLEFT OUTER JOINが無いんです！！（Join Elimination番外編） Tweet

色々な患者さんが来るわけですが、年に一度ぐらい、私のJOINが無いんです！　みたいなこともあります。

ということで、似たアトモスフィアを感じる

患者「先生、私のLEFT OUTER JOINが無いんです！！」
というところから、今回の物語は始まりますw

私「どれどれ」（触診というか問診というかw）　SQL文を診る。。
私「あ！w これは！！！！w 」

私「大丈夫ですよ！、りっぱな、 INNER JOIN です。栄養状態も良さそうですねwwww」
　「念の為、レントゲン（実行計画）も撮っておきましょう」

患者 (キョトン！)

私「ほら〜〜」

（以下、Oracle Databaseです）

SCOTT@orclpdb1> select banner_full from v$version;

BANNER_FULL
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SCOTT@orclpdb1> @where_did_my_left_outer_join_go.sql
  1  SELECT
  2    e.empno
  3  FROM
  4    emp e
  5    LEFT OUTER JOIN dept d
  6    ON
  7      e.deptno = d.deptno
  8  WHERE
  9*   d.dname = 'ACCOUNTING'

     EMPNO
----------
      7782
      7839

解析されました。

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2904284165

------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |     4 |    80 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |              |     4 |    80 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |              |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| DEPT         |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IX_DEPT_NAME |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | IX_DEPT      |     4 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | EMP          |     4 |    28 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   4 - access("D"."DNAME"='ACCOUNTING')
   5 - access("E"."DEPTNO"="D"."DEPTNO")

19行が選択されました。

って、コントはこれぐらいにして、本題。

最近、なんだか、なんでもかんでも LEFT OUTER JOINで書いちゃう病気多くないですかね。。:(

可読性も悪いし、本人は INNER JOINしてる意識があるのやら、ないのやら。 オプティマイザはしっかり反応して内部で書き換えてくれちゃってるので、 LEFT OUTER JOIN　なんて書かれてても、無駄だからそれ！　ということでINNER JOINにしてくれています。
Nested Loopが利用される場合、本当に、OUTER JOINなのであれば、外部表（駆動表）になる表は構文上から簡単に特定できますが、INNER JOINだとHASH JOIN同様に結合順を見極める作業も必要になるので要注意です。
(なお、Hash Joinでは外部表と内部表はオプティマイザヒントで入れ替え可能ですが、MySQLのHash Joinでは現状入れ替えることはできないので注意が必要です / 帰ってきた！標準はあるにはあるが癖の多いSQL #8、Hash Joinさせるにも癖が出る)

治療の難易度を上げてしまうだけで良いこともないので、OUTER/INNER は適切に使い分けてほしいものですよね。

単純な症例を一つ。MySQL/PostgreSQLでも同じ。

環境の準備 Oracle Databaseのemp/dept表を元に、制約は主キー制約、索引は以下のようになっています。（記載は略しますが、MySQL/PostgreSQLともに同様の表と索引を用意してあります）

SCOTT@orclpdb1> break on table_name on index_name skip page
SCOTT@orclpdb1> r
  1  select table_name,index_name,column_name
  2  from user_ind_columns
  3  where table_name in ('EMP','DEPT')
  4* order by table_name,index_name,column_position

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
DEPT                           IX_DEPT_NAME                   DNAME

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
DEPT                           PK_DEPT                        DEPTNO

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
EMP                            IX_DEPT                        DEPTNO

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
EMP                            PK_EMP                         EMPNO

SCOTT@orclpdb1> 
SCOTT@orclpdb1> select * from emp order by empno;

     EMPNO ENAME                          JOB                                MGR HIREDATE        SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 80-12-17        800                    20
      7499 ALLEN                          SALESMAN                          7698 81-02-20       1600        300         30
      7521 WARD                           SALESMAN                          7698 81-02-22       1250        500         30
      7566 JONES                          MANAGER                           7839 81-04-02       2975                    20
      7654 MARTIN                         SALESMAN                          7698 81-09-28       1250       1400         30
      7698 BLAKE                          MANAGER                           7839 81-05-01       2850                    30
      7782 CLARK                          MANAGER                           7839 81-06-09       2450                    10
      7839 KING                           PRESIDENT                              81-11-17       5000                    10
      7844 TURNER                         SALESMAN                          7698 81-09-08       1500          0         30
      7900 JAMES                          CLERK                             7698 81-12-03        950                    30
      7902 FORD                           ANALYST                           7566 81-12-03       3000                    20
      7934 MILLER                         CLERK                             7782 82-01-23       1300

12行が選択されました。

SCOTT@orclpdb1> select * from dept order by deptno;

    DEPTNO DNAME                                      LOC
---------- ------------------------------------------ ---------------------------------------
        10 ACCOUNTING                                 NEW YORK
        20 RESEARCH                                   DALLAS
        30 SALES                                      CHICAGO
        40 OPERATIONS                                 BOSTON

SCOTT@orclpdb1> r
  1  select table_name,constraint_name,constraint_type
  2  from user_constraints
  3  where table_name in ('EMP','DEPT')
  4* order by table_name,constraint_type

TABLE_NAME                     CONSTRAINT_NAME                CON
------------------------------ ------------------------------ ---
DEPT                           PK_DEPT                        P
EMP                            PK_EMP                         P

PostgreSQL データ量が少ない影響だと思いますが、HASH JOINになってますね。とはいえ、LEFT OUTER JOINではなく、Oracle Database同様に、INNER JOINに書き換えられています。
当然ですよね。INNER JOINなんですもの。
pg_hint_planのヒントを使ってNLJにした実行計画でも確認しておきました:)

perftestdb=> select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
(1 行)

perftestdb=> \! cat where_did_my_left_outer_join_go.sql
SELECT
  e.empno
FROM
  emp e
  LEFT OUTER JOIN dept d
  ON
    e.deptno = d.deptno
WHERE
  d.dname = 'ACCOUNTING'
;

explain 
SELECT
  e.empno
FROM
  emp e
  LEFT OUTER JOIN dept d
  ON
    e.deptno = d.deptno
WHERE
  d.dname = 'ACCOUNTING'
;

SELECT
  /*+
    Leading(d e)
    NestLoop(d e)
    IndexScan(d ix_dname)
    IndexScan(e ix_deptno)
  */
  e.empno
FROM
  emp e
  LEFT OUTER JOIN dept d
  ON
    e.deptno = d.deptno
WHERE
  d.dname = 'ACCOUNTING'
;

explain 
SELECT
  /*+
    Leading(d e)
    NestLoop(d e)
    IndexScan(d ix_dname)
    IndexScan(e ix_deptno)
  */
  e.empno
FROM
  emp e
  LEFT OUTER JOIN dept d
  ON
    e.deptno = d.deptno
WHERE
  d.dname = 'ACCOUNTING'
;
perftestdb=> \i where_did_my_left_outer_join_go.sql
 empno 
-------
  7782
  7839
(2 行)

                            QUERY PLAN                            
------------------------------------------------------------------
 Hash Join  (cost=1.06..2.27 rows=3 width=5)
   Hash Cond: (e.deptno = d.deptno)
   ->  Seq Scan on emp e  (cost=0.00..1.14 rows=14 width=10)
   ->  Hash  (cost=1.05..1.05 rows=1 width=5)
         ->  Seq Scan on dept d  (cost=0.00..1.05 rows=1 width=5)
               Filter: ((dname)::text = 'ACCOUNTING'::text)
(6 行)

 empno 
-------
  7782
  7839
(2 行)

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Nested Loop  (cost=0.27..16.39 rows=3 width=5)
   ->  Index Scan using ix_dname on dept d  (cost=0.13..8.15 rows=1 width=5)
         Index Cond: ((dname)::text = 'ACCOUNTING'::text)
   ->  Index Scan using ix_deptno on emp e  (cost=0.14..8.21 rows=4 width=10)
         Index Cond: (deptno = d.deptno)
(5 行)

perftestdb=>

MySQL MySQLもInner Joinに書き換えてますよね。だって、どこからどうみても、INNER JOINですもの。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> \! cat where_did_my_left_outer_join_go.sql
SELECT
  e.empno
FROM
  emp e
  LEFT OUTER JOIN dept d
  ON
    e.deptno = d.deptno
WHERE
  d.dname = 'ACCOUNTING'
;

explain format=tree
SELECT
  e.empno
FROM
  emp e
  LEFT OUTER JOIN dept d
  ON
    e.deptno = d.deptno
WHERE
  d.dname = 'ACCOUNTING'
;
mysql> \. where_did_my_left_outer_join_go.sql
+-------+
| empno |
+-------+
|  7782 |
|  7839 |
+-------+
2 rows in set (0.08 sec)

+-------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                   |
+-------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=0.95 rows=3.5)
    -> Filter: (d.deptno is not null)  (cost=0.35 rows=1)
        -> Covering index lookup on d using ix_dname (dname='ACCOUNTING')  (cost=0.35 rows=1)
    -> Covering index lookup on e using emp_deptno (deptno=d.deptno)  (cost=0.6 rows=3.5)
 |
+-------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

では、また。

いつまで、この酷暑が続くのやら。。。。

投稿日時 2024年8月21日 (水) 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

気まぐれ写真館

最近の記事

最近のコメント

最近のトラックバック

バックナンバー

カテゴリー