« 実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 11 / No.46 / GROUPING SETS, ROLLUP, CUBE | トップページ | 実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 13 / No.48 / MULTI-TABLE INSERT »

2022年12月12日 (月) / Author : Hiroshi Sekiguchi.

実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 12 / No.47 / TEMP TABLE TRANSFORMATION

Previously on Mac De Oracle... Day 11は, GROUPING SETS, ROLLUP, CUBEでした. ROLLUP, CUBEが登場したのは, Oracle Database 8i なので, 1999年あたりだったと思います. それまでUNIONを使ったクソ重, クソクエリーが多かった記憶はありますw ROLLUP, CUBEが神様に見えましたものw 

それでは, Day 12 の窓を開けましょう!.

今日の実行計画という名のレントゲンは, TEMP TABLE TRANSFORMATION です. 内部的に変換されて行われて現れることもあるこのOperationですが, 意図的にTEMP TABLE TRANSFORMATIONに行う場合で有名なのはCTEではないでしょうか?
類似する話題LOAD AS SELECT (CURSOR DURATION MEMORY)を取り上げていました, 内容としては11g,12c,18c,19cの差異の話。今回はその元となるTEMP TABLE TRANSFORMATIONにフォーカスをあてます。

では診ていこうと思います.

いつもと同じように 21c で確認します.


SCOTT@orclpdb1> select banner from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

ちなみに, WITH句でCTEを使ったら必ず, TEMP TABLE TRANSFORMATION でマテリアライズされるということでもなく, マテリアライズするよ理, インラインビューのままでいいよねとオプティマイザが判断してインラインビューとして展開する場合もあります. それを強制する INLINE というヒントもあります.
WITH句が利用されるケースの1つとして, 可読性向上だけを目的としている場合WITH句で定義されたクエリーが一度しか参照されていないようなケースでは, マテリアライズして一時表作るよりインラインビューで十分という判断でそのような最適化が行われます:)
逆に, マテリアライズして, 一時表に変換してほいしケースもあります. (複数回参照しているのに....みたいな場合ですね. 場合は, MATERIALIZE ヒントでオプティマイザヒントに教えてあげましょう. 最近ミスらなくなった気がしないでもない. オプティマイザも進化してますからね)

 

以下, オプティマイザはCTEと認識して, 一時表へマテリアライズした上で, 一時表を再利用しています.


SCOTT@orclpdb1> @day12
1 WITH temp_emp AS
2 (
3 SELECT
4 emp.*
5 ,dept.dname
6 FROM
7 emp
8 INNER JOIN dept
9 ON
10 emp.deptno = dept.deptno
11 )
12 SELECT
13 *
14 FROM
15 temp_emp
16 WHERE
17 temp_emp.empno = 7369
18 UNION
19 SELECT
20 *
21 FROM
22 temp_emp
23 WHERE
24* temp_emp.deptno = 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ---------- ------------------------------------------
7369 SMITH CLERK 7902 80-12-17 800 20 RESEARCH
7902 FORD ANALYST 7566 81-12-03 3000 20 RESEARCH
7566 JONES MANAGER 7839 81-04-02 2975 20 RESEARCH

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

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 768 | 6 (34)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D665A_DDA815 | | | | |
| 3 | MERGE JOIN | | 4 | 208 | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 4 | 156 | 4 (25)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | EMP | 4 | 156 | 3 (0)| 00:00:01 |
| 8 | HASH UNIQUE | | 8 | 768 | 6 (34)| 00:00:01 |
| 9 | UNION-ALL | | | | | |
|* 10 | VIEW | | 4 | 384 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D665A_DDA815 | 4 | 196 | 2 (0)| 00:00:01 |
|* 12 | VIEW | | 4 | 384 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D665A_DDA815 | 4 | 196 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

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

6 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
7 - filter("EMP"."DEPTNO"=20 OR "EMP"."EMPNO"=7369)
10 - filter("TEMP_EMP"."EMPNO"=7369)
12 - filter("TEMP_EMP"."DEPTNO"=20)

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1362 bytes sent via SQL*Net to client
52 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed

 

CTEの一時表へのマテリアライズを INLINE ヒントでインラインに展開するようにした例です. 一時表へのマテリアライズが抑止され, SQL本文に同一クエリーが展開されている様子が見えます.
WITH句がサポートされていなかった頃は, 事前に一時表を作成して使ったり苦労していたことを思うと, これも非常に便利な機能ですよね.


  1  WITH temp_emp AS
2 (
3 SELECT
4 /*+
5 inline
6 */
7 emp.*
8 ,dept.dname
9 FROM
10 emp
11 INNER JOIN dept
12 ON
13 emp.deptno = dept.deptno
14 )
15 SELECT
16 *
17 FROM
18 temp_emp
19 WHERE
20 temp_emp.empno = 7369
21 UNION
22 SELECT
23 *
24 FROM
25 temp_emp
26 WHERE
27* temp_emp.deptno = 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ---------- ------------------------------------------
7369 SMITH CLERK 7902 80-12-17 800 20 RESEARCH
7566 JONES MANAGER 7839 81-04-02 2975 20 RESEARCH
7902 FORD ANALYST 7566 81-12-03 3000 20 RESEARCH

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

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 208 | 5 (20)| 00:00:01 |
| 1 | HASH UNIQUE | | 4 | 208 | 5 (20)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 1 | 52 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 3 | 156 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 3 | 117 | 1 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IX01_EMP | 3 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

5 - access("EMP"."EMPNO"=7369)
7 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
10 - access("DEPT"."DEPTNO"=20)
12 - access("EMP"."DEPTNO"=20)

統計
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1362 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

 

サッカーの判定に利用されているVAR(Video Assistant Referee)ですが, 人の判断だと微妙なところとは人それぞれのブレが現れるので, テニスでもそうですがわかりやすいので良いんじゃないかと思いますが. ね. (ここ数日そういうブレによる判定へ意義申し立てしたりしてるw しかも見落としだし原因は. )

 

でちょいと, めんどくせーなーとなっていたりしているわけですけども.

それはさておき.

 

明日も, アドベントの担当は私ですw

ではまた.

 

参考)


SCOTT@orclpdb1> select name,inverse, sql_feature from v$sql_hint where name in ('INLINE');

NAME INVERSE SQL_FEATURE
-------------------- -------------------- ------------------------------
INLINE MATERIALIZE QKSFM_TRANSFORMATION

 

 


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

 

| |

コメント

コメントを書く