帰ってきた！ 標準はあるにはあるが癖の多いSQL #15 - 実行計画でスカラー副問合せの見せ方にも癖がでる Tweet
さて、今日はまた、癖の話をしたいと思います！
今回のネタには標準があるわけではないですが、SELECTリストに記述するスカラー副問合せの実行計画上の見せ方の癖というか違いw
実は、このネタ、2020年ぐらいに、目黒方面（ご存知の方だけwww）にある某所で定期開催される内部勉強会的なLT大会で使ったネタだったのですが、そのあとゴタゴタしていて、ブログで書き漏らしていたことを、昨日ネタリストを纏めていた時に思い出した次いでに小ネタとして書いておきます。 (その時のKeynoteのタイトルページだけ載せておきますw）
この癖を把握していれば、SELECTリストに記述されたスカラー副問合せチューニングするような案件に遭遇してしまったときでも何かの役に立つかもしれません。
（少なくとも実行計画を見ただけで、これはSELECTリストにスカラー副問合せがある！ ということは一瞬で理解できるようになるはず。。。）
では早速見てみましょう。(Oracle Databaseではお馴染みの表とデータをMySQL/PostgreSQLでも事前に作成してあります)
SCOTT@orclpdb1> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@orclpdb1> select * from emp;
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 10
実行計画で見えるSELECT中のスカラー副問合せの位置に注目してください。（赤字にしてあります）
Oracle Databaseでは本体のクエリーより上に表示されますが、PostgreSQL/MySQLでは逆で、下に表示されます。
このような見せ方の違いが逆になるのって以前もご紹介したの覚えているでしょうか？
そう、帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画の見せ方にも癖がでるで紹介した癖ですね。
HASH JOINのBUILD/PROBEは実行計画上、Oracle DatabaseとPostgreSQL/MySQLでは順序が逆に表現されていましたよね！
これに気づけば、あなたも、道にまようこともなく実行計画を追っていけるはず！！ :)
Oracle Database (21c)
（このようにスカラー副問合せ部分が性能上ネックになりそうな場合、Oracle Databaseのオプティマイザは、スカラー副問合せを結合に書き換えて最適化することがあるため、この例ではそれを無効化するNO_UNNESTヒントを利用しています。）
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SCOTT@orclpdb1> !cat scalar_subquery_plan.sql
SELECT
deptno
,dname
,(
SELECT
/*+ NO_UNNEST */
MAX(sal)
FROM
emp
WHERE
emp.deptno = dept.deptno
) AS max_sal
FROM
dept
ORDER BY
deptno
;
SCOTT@orclpdb1> set autot trace exp stat
SCOTT@orclpdb1> @scalar_subquery_plan.sql
経過: 00:00:00.18
実行計画
----------------------------------------------------------
Plan hash value: 1445953226
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 65 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 4 | 28 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX_DEPT | 4 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 5 | 65 | 3 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."DEPTNO"=:B1)
見ての通り、PostgreSQL/MySQLはスカラー副問合せ部分の実行計画の位置がOracle Databaseのそれとは異なることがわかると思います。:)
PostgreSQL(13.14)
perftestdb=> \! cat scalar_subquery_plan.sql
EXPLAIN ANALYZE
SELECT
deptno
,dname
,(
SELECT
MAX(sal)
FROM
emp
WHERE
emp.deptno = dept.deptno
) AS max_sal
FROM
dept
ORDER BY
deptno
;
perftestdb=> \i scalar_subquery_plan.sql
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Sort (cost=5.87..5.88 rows=4 width=46) (actual time=0.246..0.247 rows=4 loops=1)
Sort Key: dept.deptno
Sort Method: quicksort Memory: 25kB
-> Seq Scan on dept (cost=0.00..5.83 rows=4 width=46) (actual time=0.065..0.095 rows=4 loops=1)
SubPlan 1
-> Aggregate (cost=1.19..1.20 rows=1 width=32) (actual time=0.016..0.017 rows=1 loops=4)
-> Seq Scan on emp (cost=0.00..1.18 rows=5 width=5) (actual time=0.003..0.006 rows=4 loops=4)
Filter: (deptno = dept.deptno)
Rows Removed by Filter: 10
Planning Time: 1.916 ms
Execution Time: 0.843 ms
(11 行)
MySQL(8.0.36)
mysql> \! cat scalar_subquery_plan.sql
EXPLAIN FORMAT=tree
SELECT
deptno
,dname
,(
SELECT
MAX(sal)
FROM
emp
WHERE
emp.deptno = dept.deptno
) AS max_sal
FROM
dept
ORDER BY
deptno
;
mysql> \. scalar_subquery_plan.sql
+----------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------+
| -> Index scan on dept using PRIMARY (cost=0.65 rows=4)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: max(emp.sal) (cost=1.28 rows=1)
-> Filter: (emp.deptno = dept.deptno) (cost=1.14 rows=1.4)
-> Table scan on emp (cost=1.14 rows=14)
|
+----------------------------------------------------------------------+
1 row in set, 1 warning (0.05 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'perftestdb.dept.deptno' of SELECT #2 was resolved in SELECT #1 |
+-------+------+------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
ただし、Oracle DatabaseだけはSELECTリストのスカラー副問合せをUNNESTして結合に書き換える最適化を行うこともあるので、実行計画だけだと元のSQL文に記述されているSELECTリスト中のスカラー副問合せに気付けないこともあります.
とはいえ、一般的には、そこに至るまでの間に、SQL文は抜き出せているでしょうから困ることはないでしょうね。（現場がリモートで、実行計画だけ送られてきた！なんてことでもなければw）
SELECTリスト中に記載したスカラー副問合せがUNNESTされてMERGE JOINに書き換えられた例（UNNESTヒント利用）
2013年、Oracle Database 12cR1で実装された最適化機能で、Scalar Subquery Unnesting Transformation (Oracle Database 12c R1 New Feature)でも説明していますので、詳しく知りたい方は参考にしてみてください。
SCOTT@orclpdb1> !cat scalar_subquery_unnest.sql
SELECT
deptno
,dname
,(
SELECT
/*+ UNNEST */
MAX(sal)
FROM
emp
WHERE
emp.deptno = dept.deptno
) AS max_sal
FROM
dept
ORDER BY
deptno
;
SCOTT@orclpdb1> @scalar_subquery_unnest.sql
経過: 00:00:00.17
実行計画
----------------------------------------------------------
Plan hash value: 2834279049
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 145 | 11 (19)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 5 | 145 | 11 (19)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 65 | 3 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 5 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 | 64 | 8 (25)| 00:00:01 |
| 5 | VIEW | VW_SSQ_1 | 4 | 64 | 7 (15)| 00:00:01 |
| 6 | HASH GROUP BY | | 4 | 28 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 14 | 98 | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ITEM_1"(+)="DEPT"."DEPTNO")
filter("ITEM_1"(+)="DEPT"."DEPTNO")
Enjoy SQL! and 癖
ではまた。
関連エントリー
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）の おまけ - SQL*PlusのautotraceでSQL Analysis Reportが出力される！ (23ai〜)
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（前編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #12 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（後編）ー 列エイリアスの扱いにも癖がある！
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #13 - コメント書くにも癖がある
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #14 - コメントを書く位置にも癖がでる (SQL Clientにも癖がある)
| 固定リンク | 0
コメント