Previously on Mac De Oracle
前回はコレクション型をアクセスした場合の実行計画がどうなるのかを確認しました。
今回は少し嗜好を変えて。。
先日、Oracle Databaseの Join Elimination が行われている実行計画を、ぼーっと眺めていたのですが、、そういえば、PostgreSQL / MySQL ってどうなんだっけ？ と。気になりまして。はい。
ちょいとぐぐると、海外のブログ等では、Join Elimination - Advanced SQL tuningなど含め、PostgreSQL / MySQL 共に実装されてない。ということが書かれているのが多かったのですが、とにかく自分の目で確かめてみるか。。。ということに。。
Oracle Databaseの実行計画の話ではないですが、本「実行計画は, SQL文のレントゲン写真だ！」シリーズの番外編的な位置付けで、今回含め3回に分けた現時点の動きを確認してみます。
まずは、Oracle Databaseでの Join elimination の復習 - 無駄に結合してないですよね？
・Join Elimination（結合の排除）と 参照整合性制約 / FAQ
・実行計画は, SQL文のレントゲン写真だ！ Oracle Database (全部俺）Advent Calendar 2022 Day 18 / No.53 / Join Elimination
・join elimination（結合の排除）のバリエーション / FAQ
Oracle Databaseの主要な Join Elimination 思い出しましたか？ 復讐できましたよね！？
ということで、PostgreSQL / MySQL 含め確認していきますよ〜っ！
今回は以下のバージョンのOracle Database/PostgreSQL/MySQLを利用。（PostgreSQL、やっと16にした! w
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
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 行)
mysql>
+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.00 sec)
|
この検証では、 Oracle Database のサンプルスキーマの一つである OE スキーマから、cusotomersの一部の列、および orders表を元に scottスキーマへ複製し、参照整合性制約を追加（ orders表のcustomer_idからcustomers表の主キーを参照 )します。データはあってもなくても構わないのですが、customers/orders表に関しては別ネタで検証する際に利用することも兼ねてデータもロードしています。
（表や参照整合性など利用したオブジェクト、ロード等含めたログは、最後 ( 後日公開予定 / 実行計画は, SQL文のレントゲン写真だ！ No.63 / Join Elimination （再び）その3 ) に記載しています）
さっそく、結果から見ていきましよう（面白いですよー、そうなの！！！ いう感じではありました。Oraclerからするとw
まずは、参照整合性制約で保証されていることで、結合不要と判断される Join Elimination から。 ( db tech showcase Tokyo 2013 - A35 特濃JPOUG：潮溜まりでジャブジャブ、SQLチューニングの「参照整合性制約アレルギー」でも紹介していたので、この挙動については知っているかたは多いと思います。参照整合性制約を使ってないとお目にかかることはないタイプの Join Elimination ではあるのですけどもw )
Oracle Database / PostgreSQL / MySQL それぞれに以下のような表と主キー制約、および、参照整合性制約 (orders.customer_id -> customers.customer_id)を作成します。
Oracle Databaseでの定義内容 (なおデータ型は、MySQL/PostgreSQLに合わせて変更しています。e.g. NUMBER(n)->INTEGER or SMALLINT, VARCHAR2-> VARCHAR, TIMESTAMP WITH LOCAL TIME ZONE -> TIMESTAMP WITH TIME ZONE, TIMESTAMP)
SCOTT@orclpdb1> desc customers
名前 NULL? 型
----------------------------------------- -------- ----------------------------
CUSTOMER_ID NUMBER(6)
FIRST_NAME NOT NULL VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(20)
ADDRESS VARCHAR2(40)
PHONE_NUMBER VARCHAR2(25)
SCOTT@orclpdb1> desc orders
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ORDER_ID NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME
ZONE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NOT NULL NUMBER(6)
ORDER_STATUS NUMBER(2)
ORDER_TOTAL NUMBER(8,2)
SALES_REP_ID NUMBER(6)
PROMOTION_ID NUMBER(6)
TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
CUSTOMERS PK_CUSTOMERS CUSTOMER_ID
ORDERS FK_ORDERS_CUSTOMERS CUSTOMER_ID
ORDERS PK_ORDERS ORDER_ID
TABLE_NAME CONSTRAINT_NAME CON R_OWNER R_CONSTRAINT_NAME
------------------------------ -------------------- --- -------------------- --------------------
ORDERS FK_ORDERS_CUSTOMERS R SCOTT PK_CUSTOMERS
CUSTOMERS PK_CUSTOMERS P
ORDERS PK_ORDERS P
|
このケースで実行するSQL文はそれぞれ共通で以下を使います。
SELECT
DISTINCT
order_id
FROM
orders o
INNER JOIN customers c
ON o.customer_id = c.customer_id
WHERE
order_id < 2400;
|
Oracle Database (21c) customers表は結合されず、join elimination されていることがわかります。inner join で保証しようとしている orders 表に存在している order_idだcustomer表に存在している顧客の注文であるということが参照整合性制約で保証されているため、結合は不要と判断されたわけです。
参照整合性制約アレルギーのみなさんには耳の痛い話ではありますが、この制約のメリットの一つは、Join Eliminationだったりします。
話は少し脱線しますが、発症すると一生ものの参照整合性制約アレルギーなのでw うまく付き合っていきたいものですよね。使いたい！と思えなくなってしまうものなので、Pros/Consをよーーーーーく考えて上で判断したい仕組みですよね。
SCOTT@orclpdb1> l
1 EXPLAIN PLAN FOR
2 SELECT
3 DISTINCT
4 order_id
5 FROM
6 orders o
7 INNER JOIN customers c
8 ON o.customer_id = c.customer_id
9 WHERE
10* order_id < 2400
SCOTT@orclpdb1> /
解析されました。
経過: 00:00:00.01
SCOTT@orclpdb1> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 2834288864
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 184 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| PK_ORDERS | 46 | 184 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ORDER_ID"<2400)
13行が選択されました。
|
最近のコメント