実行計画は, SQL文のレントゲン写真だ! Oracle Database (全部俺)Advent Calendar 2022 Day 5 / No.40 / PIVOT and UNPIVOT Tweet
Previously on Mac De Oracle...
Day 4は, SQLというより, in-memory operationに追加された, In-Memory Hybrid Scans の実行計画という名のレントゲンをみながら, どのような状況で発動するのかを簡単に確認しましたー.
なかな面白い動きでしたね. 別途時間を取って深掘りしないとね, と. :)
ということで, Day 5 の窓を開けましょう. 今日は, 新機能というより以前からあったのにレントゲン取ってなかったよね? ということで PIVOT and UNPIVOT を診てみたいと思います.
いつもと同じように 21c で確認します.
SCOTT@orclpdb1> select banner from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
最初は, pivot 向けデータの準備から
SCOTT@orclpdb1> @day5
表が削除されました.
1 CREATE TABLE for_pivot
2 AS
3 SELECT
4 stat_name
5 , value
6 FROM
7 v$sys_time_model
8 WHERE
9 stat_name IN (
10 'DB time'
11 , 'DB CPU'
12 , 'background elapsed time'
13 , 'background cpu time'
14* )
表が作成されました.
1* CREATE INDEX ix_for_pivot ON for_pivot(stat_name)
索引が作成されました.
1 SELECT
2 *
3 FROM
4* for_pivot
STAT_NAME VALUE
------------------------------ ----------
DB time 143415979
DB CPU 127540525
background elapsed time 7381483
background cpu time 6340262
/code>
PIVOTで行持ちを列持ちに変換してみます.
おお, 実行計画には, PIVOT とか出ないのですよーーーー!! (知ってましたか!
こいつも実行計画を見ただけでは, PIVOTが行われているかは読み取れないですねw アクセスパス以外は注意する箇所はあまりないわけですけども:)
1 SELECT
2 db_time
3 ,db_cpu
4 FROM
5 (
6 SELECT
7 stat_name
8 , value
9 FROM
10 for_pivot
11 WHERE
12 stat_name IN ('DB time', 'DB CPU')
13 )
14 PIVOT
15 (
16 MAX(value)
17 FOR stat_name IN
18 (
19 'DB time' AS db_time
20 ,'DB CPU' AS db_cpu
21 )
22* )
DB_TIME DB_CPU
---------- ----------
143415979 127540525
実行計画
----------------------------------------------------------
Plan hash value: 1690715989
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 22 | | |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| FOR_PIVOT | 2 | 44 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IX_FOR_PIVOT | 2 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("STAT_NAME"='DB CPU' OR "STAT_NAME"='DB time')
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
677 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)
1 rows processed
/code>
次に, UNPIVOT 向けデータの準備
表が削除されました.
1 CREATE TABLE for_unpivot
2 AS
3 SELECT
4 db_time
5 ,db_cpu
6 ,bg_time
7 ,bg_cpu
8 FROM
9 (
10 SELECT
11 stat_name
12 ,value
13 FROM
14 v$sys_time_model
15 )
16 PIVOT
17 (
18 MAX(value)
19 FOR stat_name IN
20 (
21 'DB time' AS db_time
22 ,'DB CPU' AS db_cpu
23 ,'background elapsed time' AS bg_time
24 ,'background cpu time' AS bg_cpu
25 )
26* )
表が作成されました.
1* COMMIT
コミットが完了しました.
1 SELECT
2 *
3 FROM
4* for_unpivot
DB_TIME DB_CPU BG_TIME BG_CPU
---------- ---------- ---------- ----------
143522885 127634777 7381483 6340262
/code>
UNPIVOT では, PIVOT とは異なり, 実行計画上 UNPIVOT であることが読み取れます!!! ここ試験に出ますよ!(嘘w
1 SELECT
2 stat_name
3 , value
4 FROM
5 for_unpivot
6 UNPIVOT
7 (
8 value FOR stat_name IN
9 (
10 db_time
11 , db_cpu
12 , bg_time
13 , bg_cpu
14 )
15 )
16* ORDER BY stat_name
STAT_NAME VALUE
------------------------------ ----------
BG_CPU 6340262
BG_TIME 7381483
DB_CPU 127634777
DB_TIME 143522885
実行計画
----------------------------------------------------------
Plan hash value: 3659757171
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 6 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 4 | 72 | 6 (17)| 00:00:01 |
|* 2 | VIEW | | 4 | 72 | 5 (0)| 00:00:01 |
| 3 | UNPIVOT | | | | | |
| 4 | TABLE ACCESS FULL| FOR_UNPIVOT | 1 | 26 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("unpivot_view_005"."VALUE" IS NOT NULL)
統計
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
781 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)
4 rows processed
/code>
ということで, Operation には, なぜ, UNPIVOT は現れるのに, PIVOT が現れないのだろう. . . その謎を求め, 一向は洞窟の奥深くへ進んでいくのであった. . . .
昔からサポートされている PIVOT / UNPIVOT ですが, 改めて実行計画というレントゲンを診てみると, 新しい気づきとかあって良いですね.
参考 SQL Language Reference / PIVOT and UNPIVOT
Oracle Database 11g:Oracle ACEディレクター, Arup Nanda - PIVOT and UNPIVOT
ということで, 明日の担当は, またまた私です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
| 固定リンク | 0
コメント