実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 24 / No.59 / SQL MACRO (19.7〜) Tweet
Previously on Mac De Oracle...
Day 23は, ANTI JOINにフォーカスをあてました. そうくれば, 次は, SEMI JOINだろう? と思ったあなた! ハズレですw
では, Day 24 クリスマスイブの窓を開けましょう!
実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 9 / No.44 / COLLECTION ITERATOR PICKLER FETCH
で, パイプラインテーブルファンクションだと, ファンクションが実行されていることしかExplain等で見る事はできなくて, COLLECTION ITERATOR PICKLER FETCH だけなんだよね, と言うお話をしました.
1 SELECT
2 *
3 FROM
4* day9_pkg.list_2_latest_sales(2, 20, 5745)
PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- -------- ---------- ---------- ------------- -----------
20 5745 01-12-31 2 999 1 628.89
実行計画
----------------------------------------------------------
Plan hash value: 457385954
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| LIST_2_LATEST_SALES | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
レントゲン屋さん的にはw 辛いよね. と. 間接的に見るしかないわけで. 少々面倒, 対応を意識していないと難しいですし.
今日は, クリスマスイブですし, Oracleさんもどんどん新機能提供してくれてて, それファンクションじゃないの? いえ, いえ. ?? 今日は, SQLマクロ(SQM)の実行計画という名のレントゲンです.
SQLマクロ(SQM)を作ります. パイプラインテーブルファンクションと似てますけども, SQL_MACROです.
SCOTT@orclpdb1> @day24
1* DROP FUNCTION sq_macro_sample
ファンクションが削除されました.
1 CREATE FUNCTION sq_macro_sample
2 (
3 p_first_rows NUMBER
4 , p_table_name DBMS_TF.TABLE_T
5 )
6 RETURN VARCHAR2
7 SQL_MACRO
8 IS
9 BEGIN
10 RETURN
11 'SELECT
12 *
13 FROM
14 p_table_name
15 FETCH FIRST sq_macro_sample.p_first_rows ROWS ONLY'
16 ;
17* END;
ファンクションが作成されました.
おおおおおおおおおお〜っと. マクロが展開されて, emp表の直接アクセスしている. (マクロだからそりゃそうだw)
レントゲンを見てみましょう.
パイプラインテーブルファンクションと外見からはあまり違いに気づかないというか, 気づけないですが. レントゲンの結果は全く違います! マクロが展開されて, 普通の SQL文の実行計画という名のレントゲンを見ることができます.
1 SELECT
2 *
3 FROM
4* sq_macro_sample(3, 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
実行計画
----------------------------------------------------------
Plan hash value: 3611411408
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 300 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 3 | 300 | 2 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 3 | 117 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 3 | 117 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_009"."rowlimit_$$_rownumber"<=3)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=3)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1262 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
おおおおおおおおおお〜っと. 2回目w マクロが展開されて, dept表の直接アクセスしている. (マクロだからそりゃそうだw)
1 SELECT
2 *
3 FROM
4* sq_macro_sample(2, dept)
DEPTNO DNAME LOC
---------- ------------------------------------------ ---------------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
実行計画
----------------------------------------------------------
Plan hash value: 3452784451
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 86 | 3 (0)| 00:00:01 |
|* 1 | VIEW | | 2 | 86 | 3 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY| | 2 | 40 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | DEPT | 2 | 40 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_009"."rowlimit_$$_rownumber"<=2)
2 - filter(ROW_NUMBER() OVER ( ORDER BY NULL )<=2)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
827 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
みなさん, SQL_MACRO, 実は, 物凄く, いいいやつ, っぽい感じがします. 慣れるまでは敷居高そうでもありますがw
マニュアルを真面目に読んで, 倒れそうになりました. (斜め読みでは理解しきれない深さを感じます)
入門としては, HNakaieさんのエントリがわかりやすいですかね.
Oracle DatabaseのSQLマクロを検証する /
参考)
Oracle Database 21c Database PL/SQL Language Reference / 14.64 SQL_MACRO Clause
ということで, Happy Holidays中の方も, Hard Wroking Now!な方も, I wish you all a Merry Christmas.
そういえば, もう1週間以上前ですがw, アメリカのOracleの中の人からのメッセージが, Happy Holidays! で締めくくられていました.
日本人の感覚だと, え, と思わなくもないわけですが, アメリカとかだとそうだよねーーー. そう言う時期だよねーーと, 思いつつ,
アドベントカレンダー全部俺のネタ作りに追われているw 俺, なんなの? みたいに思ったりw
では, アドベントカレンダー最終日の担当も, 私です.
ではまた.
Related article on Mac De Oracle
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 1 / TABLE FULL SCAN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 2 / INDEX UNIQUE SCAN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 3 / INDEX RANGE SCAN, Index Only Scan
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 4 / INDEX RANGE SCAN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 5 / INDEX RANGE SCAN, INLIST ITERATOR
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 6 / INDEX FAST SCAN, Index Only Scan
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 7 / INDEX FULL SCAN,Index Only Scan
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 8 / INDEX SKIP SCAN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 9 / TABLE ACCESS INMEMORY FULL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 10 / NESTED LOOP JOIN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 11 / MERGE JOIN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 12 / HASH JOIN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 13 / HASH JOIN OUTER
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 14 / HASH JOIN FULL OUTER
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 15 / PX, TABLE ACCESS FULL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 16 / CONCATENATION
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 17 / SORT UNIQUE, UNION-ALL = UNION
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 18 / UNION-ALL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 19 / INTERSECTION
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 20 / MINUS
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 21 / WINDOW NOSORT STOPKEY
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 22 / COUNT STOPKEY
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 23 / HASH JOIN - LEFT-DEEP JOIN vs RIGHT-DEEP JOIN
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 24 / CONNECT BY NO FILTERING WITH START-WITH
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - Day 25 / UNION ALL (RECURSIVE WITH) DEPTH FIRST, RECURSIVE WITH PUMP
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#1 / STAR TRANSFORM, VECTOR TRANSFORM (DWH向け)
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#2 / MERGE (UPSERT)
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#3 / RDFView
・実行計画は, SQL文のレントゲン写真だ! Oracle Database編 (全部俺)Advent Calendar 2019 - おまけ#4 / INDEX FULL SCAN (MIN/MAX) - Index Only Scan
・実行計画は, SQL文のレントゲン写真だ! No.30 - LOAD TABLE CONVENTIONAL vs. LOAD AS SELECT
・実行計画は, SQL文のレントゲン写真だ! No.31 - TEMP TABLE TRANSFORMATION LOAD AS SELECT (CURSOR DURATION MEMORY)
・実行計画は, SQL文のレントゲン写真だ! No.32 - EXTERNAL TABLE ACCESS FULL / INMEMORY FULL
・実行計画は, SQL文のレントゲン写真だ! No.33 - BITMAP CONVERSION TO ROWIDS
・実行計画は, SQL文のレントゲン写真だ! No.34 - 似て非なるもの USE_CONCAT と OR_EXPAND ヒント と 手書きSQLのレントゲンの見分け方
・実行計画は, SQL文のレントゲン写真だ! No.35 - 似て非なるもの USE_CONCAT と OR_EXPANDヒントとパラレルクエリー
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 1 / No.36 / INTERSECT ALL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 2 / No.37 / MINUS ALL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 3 / No.38 / EXCEPT and EXCEPT ALL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 4 / No.39 / In-Memory Hybrid Scans
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 5 / No.40 / PIVOT and UNPIVOT
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 6 / No.41 / In-Memory Vectorized Join
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 7 / No.42 / INDEX RANGE SCAN (MULTI VALUE)
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 8 / No.43 / TABLE ACCESS BY INDEX ROWID BATCHED
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 9 / No.44 / COLLECTION ITERATOR PICKLER FETCH
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 10 / No.45 / MAT_VIEW REWRITE ACCESS FULL
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 11 / No.46 / GROUPING SETS, ROLLUP, CUBE
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 12 / No.47 / TEMP TABLE TRANSFORMATION
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 13 / No.48 / MULTI-TABLE INSERT
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 14 / No.49 / the DUAL Table
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 15 / No.50 / REMOTE
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 16 / No.51 / Concurrent Execution of Union All and Union
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 17 / No.52 / Order by Elimination
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 18 / No.53 / Join Elimination
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 19 / No.54 / Group by Elimination
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 20 / No.55 / DISTINCT Elimination
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 21 / No.56 / INLIST ITERATOR と Sub Query と STATISTICS COLLECTOR
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 22 / No.57 / Subquery Unnesting
・実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 23 / No.58 / ANTI JOIN
| 固定リンク | 0
コメント