join elimination（結合の排除）のバリエーション / FAQ Tweet

Previously on Mac De Oracle
SQL*Plus -Fastオプション / FAQでした。

今日は、結合排除のバリエーションをいくつか紹介しておこうと思います。
なんどか説明していた無駄に結合してないですよね？ の例は、参照整合性制約を頼りに結合排除が行われるものでした。

今日はそれに加えて、典型的な例を2つ紹介しておきたいと思います。（ここ”　”、試験に出ませんよ！w　でないけど大切：）

一つ目は、結合列がそれぞれユニークキーかプライマリキーで一意で、1 : 0..1 で外部結合されるケース
結合の排除は、その結合を排除しても結果に影響しなことが自明な場合に発動するので、条件を満たしています。ただ、参照整合性制約のパターン同様に、SQL文を見ただけでは気づけないですよね。
あれ、実行計画にSQL文に記載されている表がない！？　で制約を見てみて、あ”〜〜〜〜理解。みたいなw

実行計画ではSQLモニターも含め、Join Eliminationしたことを明示的に示すコメント等はありません。結合が消えていることで気づくことが多いわけですw（よーく考えたら、その結合イラねーじゃんというわけですけどもw0

  1* CREATE TABLE foo (id NUMBER PRIMARY KEY, note VARCHAR2(100))

表が作成されました。

  1* CREATE TABLE bar (id NUMBER PRIMARY KEY, memo VARCHAR2(100))

表が作成されました。

  1  SELECT
  2    foo.id
  3    , foo.note
  4  FROM
  5    foo
  6    LEFT OUTER JOIN bar
  7    ON
  8*     foo.id = bar.id

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

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

この手の制約による結合の排除は、前提になる制約が結合排除の条件からはずれると排除されなくなります。
たとえば、前述の例で、それぞれの表を主キーで　1:0..1 で外部結合していましたよね？
それが仕様変更で、片方の方の主キーが複合主キーになってしまい。1:* の外部結合になってしまうと。。。。結合排除できなくなります。排除した場合、クエリー結果に影響するから。

  1  CREATE TABLE foo2 (
  2    id NUMBER
  3   , note VARCHAR2(100)
  4   , PRIMARY KEY (id)
  5* )

表が作成されました。

  1  CREATE TABLE bar2 (
  2    id NUMBER
  3   , sq NUMBER NOT NULL
  4   , memo VARCHAR2(100)
  5   , PRIMARY KEY (id, sq)
  6* )

表が作成されました。

  1  SELECT
  2    foo2.id
  3    , foo2.note
  4  FROM
  5    foo2
  6    LEFT OUTER JOIN bar2
  7    ON
  8*     foo2.id = bar2.id

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

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    78 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|             |     1 |    78 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| FOO2        |     1 |    65 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | SYS_C009222 |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   3 - access("FOO2"."ID"="BAR2"."ID"(+))

では、結合の排除のもう一つの例。これは制約というより、排除を狙った意図的な方法です。
動的なSQlが利用されているアプリケーションで使われていることが多い印象ですが、この方法は好き嫌い激しいかもしれないですね。私は好きじゃないですw　が偶にみます。

このクエリは、1:* の外部結合なのですが、WHERE 1=0　によって結果を返しません。なのでクエリー結果に影響しないことが自明なので排除されている。ということになります。

   1  SELECT
   2    foo2.id
   3   , foo2.note
   4  FROM
   5    foo2
   6    LEFT OUTER JOIN
   7    (
   8       SELECT * FROM bar2 WHERE 1=0
   9    ) bar2
  10    ON
  11*     foo2.id = bar2.id

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

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

 Note
 -----
    - dynamic statistics used: dynamic sampling (level=2)

ということで、今年も残すところあと11ヶ月ですねw
長いことやってたプロジェクトが一つ形になったみたいな、ならないような、hear through the grapevine.

では、また。

---

・Join Elimination（結合の排除）と 参照整合性制約　/ FAQ
・実行計画は, SQL文のレントゲン写真だ！　Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination