2021.10.21
SQLチューニング（第16回）「WITH節理解と効率的なSQLの作成」(1/3)
今回からWITH節に関する考察が始まります。今回は、その1回目として「WITH節動作方式を理解すること」をお送りします。では早速始めましょう。
4. WITH節理解と効率的なSQLの作成」
With節は同じデータを繰り返し読まなければならないSQLの性能を改善するための方法でたくさん使われる。 一般的にWith節を使えば常にGlobal Temporary Tableを生成した後に抽出された結果セットを保存して、この保存されたデータをSQLで使うことで正しく知らない場合が多い。
しかし、With節はGlobal Temporary Table (Materialize)を使う方式の他にInline View方式でも実行される。 それぞれの動作方式に対する説明は以後に詳しく説明する。
11g以前のバージョンではWith節に対してInline View動作方式を選択するのか、Materialize動作方式を選択するのかに対する判断をOptimizerが決めるが、その判断基準はWith節がSQLで実行される回数である。 With節で抽出した結果セットをSQLで2回以上実行する場合Materialize動作方式を、1回だけ実行する場合Inline View動作方式で実行する。 With節は状況によって動作方式が二種類に分かれるので、SQL性能と密接な関連がある場合には使用者が強制的に動作方式を調節する必要がある。 With節動作方式を調節するヒントはINLINEとMATERIALIZEがある。 11gでは隠しパラメーターである”_WITH_SUBQUERY”によっても制御が可能である。
4.1 WITH節動作方式を理解すること
4.1.1 MATERIALIZE動作方式
Materialize動作方式は先にGlobal Temporary Tableを生成した後、With節で抽出した結果セットを保存する。 そしてMain SQLでWith節を呼び出せば、抽出した結果セットが保存されているGlobal Temporary Tableを読んでデータを処理する。 理解を助けるためにテストを通じて調べてみる。 まずテスト スクリプトを実行する。
Script. WITH節テスト用
< WITH_T1 >
■生成要件
-テーブル件数は500,000行
-カラムC1は値の種類が500,000種類であり、Uniqueである。
-カラムC2は値の種類が26種類でありアルファベットである。
-カラムC3は値の種類が500,000種類であり10,000から次々に増加する。
■テーブルの作成
CREATE TABLE WITH_T1
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2, LEVEL+9999 AS C3
FROM DUAL
CONNECT BY LEVEL <= 500000 ;
■各カラムにインデックス生成および統計情報収集
CREATE INDEX WITH_T1_IDX_01 ON WITH_T1 ( C1 ) ;
CREATE INDEX WITH_T1_IDX_02 ON WITH_T1 ( C2, C3 ) ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'WITH_T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
< WITH_T2 >
■生成要件
-テーブル件数は500,000ロー
-カラムC1は値の種類が500,000種類であり、Uniqueする。
-カラムC2は値の種類が26種類でありアルファベットである。
-カラムC3は値の種類が10種類でありNULLデータを含む。
■テーブル生成
CREATE TABLE WITH_T2
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2,
DECODE(MOD(LEVEL,10),0,NULL,MOD(LEVEL,10)) AS C3
FROM DUAL
CONNECT BY LEVEL <= 500000 ;
■各カラムにインデックス生成および統計情報収集
CREATE INDEX WITH_T2_IDX_01 ON WITH_T2 ( C1 ) ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'WITH_T2',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
< WITH_T3 >
■生成要件
-テーブル件数は500,000ロー
-カラムC1は値の種類が500,000種類であり、Uniqueする。
-カラムC2は値の種類が26種類でありアルファベットである。
-カラムC3は値の種類が100種類でありNULLデータを含む。
■テーブル生成
CREATE TABLE WITH_T3
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2,
LEVEL+DECODE(MOD(LEVEL,100),0,NULL,MOD(LEVEL,100)) AS C3
FROM DUAL
CONNECT BY LEVEL <= 500000 ;
■各カラムにインデックス生成および統計情報収集
CREATE INDEX WITH_T3_IDX_01 ON WITH_T3 ( C1 ) ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'WITH_T3',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
< WITH_T4 >
■生成要件
-テーブル件数は1,000,000ロー
-カラムC1は値の種類が1,000,000種類であり、UNIQUEする。
-カラムC2は値の種類が26種類でありアルファベットである。
-カラムC3は値の種類が100種類でありNULLデータを含む。
■テーブル生成
CREATE TABLE WITH_T4
AS
SELECT LEVEL AS C1, CHR(65+MOD(LEVEL,26)) AS C2,
LEVEL+DECODE(MOD(LEVEL,100),0,NULL,MOD(LEVEL,100)) AS C3
FROM DUAL
CONNECT BY LEVEL <= 1000000 ;
■各カラムにインデックス生成および統計情報収集
CREATE INDEX WITH_T4_IDX_01 ON WITH_T4 ( C2, C3 ) ;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'WITH_T4',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
[実行方法]
・ STEP1. GLOBAL TEMPORARY TABLE生成
・ STEP2. GLOBAL TEMPORARY TABLEにWITH節で抽出したデータINSERT
・ STEP3. GLOBAL TEMPORARY TABLEを問い合わせてデータ処理
[制御ヒント:/*+ MATERIALIZE*/]
WITH T_T1 AS (
SELECT *
FROM T1
WHERE c2 IN ('A','B','C')
), T_T2 AS (
SELECT *
FROM T2
WHERE c2 IN ('A','B','C')
AND c3 <= 10
)
SELECT t1.*,
t2.*
FROM T_T1 T1,
T_T2 T2
WHERE t1.c1 = T2.c1
AND t1.c2 = 'A' ;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1092 | 17472 | 335 (2)| 00:00:05 |
|* 1 | HASH JOIN | | 1092 | 17472 | 335 (2)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T2 | 1092 | 10920 | 277 (1)| 00:00:04 |
|* 3 | TABLE ACCESS FULL| T1 | 3870 | 23220 | 57 (4)| 00:00:01 |
---------------------------------------------------------------------------
前で説明したようにOptimizerはWith節で抽出した結果セットをSQLで2回以上使ってこそMaterialize動作方式で処理する。 しかし、上の例題SQLはWith節T_T1,T_T2に対する照会がそれぞれ1回だけ実行されたのでMaterialize動作方式でないInline View動作方式で実行された。 するとWith節にMATERIALIZEヒントを付与すればどうなろうか? 直接ヒントを付与して実行してみよう。
WITH T_T1 AS (
SELECT /*+ MATERIALIZE */
*
FROM T1
WHERE c2 IN ('A','B','C')
), T_T2 AS (
SELECT /*+ MATERIALIZE */
*
FROM T2
WHERE c2 IN ('A','B','C')
AND c3 <= 10
)
SELECT T1.*,
T2.*
FROM T_T1 T1,
T_T2 T2
WHERE t1.c1 = t2.c1
AND t1.c2 = 'A' ;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122 | 5490 | 00:00:05 |
| 1 | TEMP TABLE TRANSFORMATION | | | | |
| 2 | LOAD AS SELECT | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 11172 | 67032 | 00:00:01 |
| 4 | LOAD AS SELECT | | | | |
|* 5 | TABLE ACCESS FULL | T2 | 1092 | 10920 | 00:00:04 |
|* 6 | HASH JOIN | | 122 | 5490 | 00:00:01 |
| 7 | VIEW | | 1092 | 31668 | 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6683| 1092 | 10920 | 00:00:00 |
|* 9 | VIEW | | 11172 | 174K| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6682| 11172 | 67032 | 00:00:00 |
-------------------------------------------------------------------------------
With節をSQLでそれぞれ1回使ったが、MATERIALIZEヒントを付与するとすぐにGlobal Temporary Tableを利用するMaterialize動作方式で実行されたのを確認することができる。
4.1.2 INLINE VIEW動作方式
Inline View動作方式はWith節で抽出した結果セットをSQLで1回使われる場合Global Temporary Tableを使わないで、Inline View方式で実行される方式である。 以下の例題を通じて詳しく調べてみる。
[実行方法]
・ With節で抽出されたデータをGlobal Temporary Tableに保存しないで、Inline Viewで実行される。
[制御ヒント:/*+ INLINE*/]
WITH T_T1 AS (
SELECT *
FROM T1
WHERE c2 IN ('A','B','C')
), T_T2 AS (
SELECT *
FROM T2
WHERE c2 IN ('A','B','C')
AND c3 <= 10
)
SELECT T1.*,
T2.*
FROM T_T1 T1,
T_T2 T2
WHERE t1.c1 = t2.c1
AND t1.c2 = 'A'
UNION ALL
SELECT T1.*,
T2.*
FROM T_T1 T1,
T_T2 T2
WHERE t1.c1 = t2.c1
AND t1.c2 = 'B' ;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 244 | 10980 | 13 (54)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 11172 | 67032 | 58 (6)| 00:00:01 |
| 4 | LOAD AS SELECT | | | | | |
|* 5 | TABLE ACCESS FULL | T2 | 1092 | 10920 | 277 (1)| 00:00:04 |
| 6 | UNION-ALL | | | | | |
|* 7 | HASH JOIN | | 122 | 5490 | 7 (15)| 00:00:01 |
| 8 | VIEW | | 1092 | 31668 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D| 1092 | 10920 | 2 (0)| 00:00:00 |
|* 10 | VIEW | | 11172 | 174K| 4 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D| 11172 | 67032 | 4 (0)| 00:00:00 |
|* 12 | HASH JOIN | | 122 | 5490 | 7 (15)| 00:00:01 |
| 13 | VIEW | | 1092 | 31668 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D| 1092 | 10920 | 2 (0)| 00:00:00 |
|* 15 | VIEW | | 11172 | 174K| 4 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D| 11172 | 67032 | 4 (0)| 00:00:00 |
------------------------------------------------------------------------------
上記SQLはWith節T_T1,T_T2がそれぞれSQLで2回以上使われて、Materialize動作方式で実行された。 しかし、INLINEヒントを付与すれば実行計画にどんな変化がおきるだろうか? 直接ヒントを付与して実行計画を確認してみよう。
WITH T_T1 AS (
SELECT /*+ INLINE */
*
FROM T1
WHERE c2 IN ('A','B','C')
), T_T2 AS (
SELECT /*+ INLINE */
*
FROM T2
WHERE c2 IN ('A','B','C')
AND c3 <= 10
)
SELECT T1.*,
T2.*
FROM T_T1 T1,
T_T2 T2
WHERE t1.c1 = t2.c1
AND t1.c2 = 'A'
UNION ALL
SELECT T1.*,
T2.*
FROM T_T1 T1,
T_T2 T2
WHERE t1.c1 = t2.c1
AND t1.c2 = 'B' ;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2184 | 34944 | 669 (51)| 00:00:09 |
| 1 | UNION-ALL | | | | | |
|* 2 | HASH JOIN | | 1092 | 17472 | 335 (2)| 00:00:05 |
|* 3 | TABLE ACCESS FULL| T2 | 1092 | 10920 | 277 (1)| 00:00:04 |
|* 4 | TABLE ACCESS FULL| T1 | 3870 | 23220 | 57 (4)| 00:00:01 |
|* 5 | HASH JOIN | | 1092 | 17472 | 335 (2)| 00:00:05 |
|* 6 | TABLE ACCESS FULL| T2 | 1092 | 10920 | 277 (1)| 00:00:04 |
|* 7 | TABLE ACCESS FULL| T1 | 3687 | 22122 | 57 (4)| 00:00:01 |
----------------------------------------------------------------------------
INLINEヒントを付与して、With節は既存のMaterialize動作方式がInline View動作方式に変更された。 今まで基本的なWith節の動作方式とこれを制御できるヒントに対して調べてみた。 以降With節がSQLの性能を改善する目的でどのように活用できるのか調べてみることにしよう。
今回は、ここまでです。本文にある通り次回は、W ITH節の活用について考察します。ご期待ください。See You^^
