2022.02.02
SQLチューニング（第23回）「DECODE & CASE WHENの理解および条件ステートメントの処理」(1/2)
今回から２回シリーズで「DECODE & CASE WHENの理解および条件ステートメントの処理」と題しまして、お話を進めます。第１回目は「DECODE」についてです。では、早速はじめましょう。
７.１ DECODE
DECODE関数(以後DECODE)はSQL内でIF – THEN – ELSE IF – ENDロジックを使えるようにOracleで提供する関数です。 DECODEは特定条件のTRUEあるいはFALSE結果によりそれぞれ違った値を抽出する場合に非常に便利に使われて、SQL内のFrom句を除いてすべての部分で使用可能です。 また、DECODEを使えば複雑なSQLをより簡潔に作成することができるという長所があります。
DECODEは長所が多くありますが、どのように使うかにより性能問題を発生させる原因になることもあります。 DECODEはすでに広く使われていますが、DECODEを使ったSQLが長くなる場合、まだ正確にどんな手続きで処理をするのかに対して紛らわしい場合が多くあります。 今回の場ではDECODEの正確な構文と使用方法はもちろん、DECODEが及ぼす性能問題に対して調べてみることにより、DECODEをより正確で効率的に使用できる方法に対して調べてみることにしましょう。
７.１.１ 構文
DECODE({column | expression}, search1, result1 [,search2,result2] ... [,default] )７.１.２ 構文に対する詳細説明
（１）COLUMN | EXPRESSION
テーブルのカラムや計算式などを使うことができる。
（２）SEARCH1
Column | Expressionの結果と比較する値を指定すします。
例)DECODE(9+1(COLUMN|EXPRESSION),10(SEARCH1),'正解')計算式9+1の結果値とSEARCH1値が同じなのか比較する目的で使用され、例文でSEARCH1に該当する値は10であう。 すなわち、9+1が10と同じなのかを比較します。
（３）RESULT1
COLUMN | EXPRESSIONとSEARCH1値が同じならばリターンする値を指定する部分です。
以前の例題で9+1課比較値10が同一なので”正解”をリターンすることになります。
もし、SEARCH1の値が11だったら、FALSEになってNULLをリターンすることになります。
DECODE(COLUMN | EXPRESSION,SEARCH1,RESULT1)因子が3個の場合PL/SQLプログラムのIF,THEN節で表現すれば下記の通りとなります。 ①、②は実行順序を意味します。
| DECODE ( 9+1,10,‘正解’ ) ①② |
| IF 9+1 = 10 THEN① |
| RETURN ‘正解’② END IF; |
（４）[,SEARCH2,RESULT2 … ]
SEARCH2とRESULT2は常に一緒に記述され、IF節でELSIFに該当すると考えれば良いです。 COLUMN | EXPRESSIONとSEARCH1を比較した後TRUEでなければ、SEARCH2と同じなのか比較して、比較結果がTRUEならRESULT2をリターンします。
例)DECODE (9+1,9,‘正解1’、10,‘正解2’)計算式9+1を9と比較してみればFALSEです。 したがってSEARCH2と比較します。 9+1と10を比較すればTRUEであるから”正解2″をリターンします。 もし、二回の比較が全部FALSEだったらNULLをリターンすることになります。
これをPL/SQLで表現すれば下記の通りです。
| DECODE ( 9+1,9,‘正解1’、10,‘正解2’ ) ①②③④ |
| IF 9+1 = 10 THEN① RETURN ‘正解1’② ELSIF 9+1 = 10 THEN③ RETURN ‘正解2’④ END IF; |
（５）[, DEFAULT]
DEFAULTはIF文のELSE節と同じ役割をはたします。 すなわち、IF-ELSIF条件比較に対して全てFALSEである時NULL値でないリターン値を指定する場合に使用します。
例)DECODE (9+2,9,‘正解1’、10,‘正解2’、‘正解3’)この例題では計算式9+2と9,10をそれぞれ比較した結果が全部FALSEですのでDEFAULTに指定した”正解3″をリターンすることになりますす。
これをPL/SQLで表現すれば次のとおりです。
| DECODE ( 9+1,9,‘正解1’、10,‘正解2’、‘正解3’ ) ①②③④⑤ |
| IF 9+1 = 9 THEN① RETURN ‘正解1’② ELSIF 9+1 = 10 THEN③ RETURN ‘正解2’④ ELSE RETURN ‘正解3’⑤ END IF; |
７.１.３ DECODEネスト処理
DECODEはネスト処理が可能で、より細かい制御を行うことができます。
例)SELECT deptno,
sal ,
DECODE( deptno , 30 , DECODE( sal , 2200 , 'DEPTNO=30 AND SAL=2200' ,
'DEPTNO=30 AND SAL<>2200' ) , 'DEPTNO <> 30' )
FROM emp ;
上記の例でDEPTNOが30の場合、別のDECODEをネストすることになります。 このようにネストされた場合、DECODEがどのように処理されるかを調べましょう。
もし、DEPTNO値が30であれば、SALが2200かどうか比較し、SALが2200であれば、「DEPTNO=30 AND SAL=2200」を返します。 しかし、SALが2200でない場合は、「DEPTNO = 30 AND SAL <> 2200」を返します。 もし DEPTNO が 30 でない場合は、入れ子になった DECODE を処理せずに DEFAULT 値の「DEPTNO<>30」文字列を返します。 入れ子になった DECODE を PL/SQL で表現すると以下の通りです。
IF DEPTNO = 30 THEN
IF SAL = 2200 THEN
RETURN ‘DEPTNO=30 AND SAL=2200’;
ELSE
RETURN ‘DEPTNO=30 AND SAL<>2200’;
END IF;
ELSE
RETURN ’DEPTNO <> 30’;
END IF;
７.１.４ DECODEとパフォーマンスの問題
７.１.４.１ ローをカラムに変換時サブクエリを利用したSQL作成
先にテストのためにDECODE_T1テーブルとデータを生成することにしましょう。
Script. DECODEテスト用
DROP TABLE DECODE_T1;
< DECODE_T1 >
■生成要件
-テーブル データ件数は約1,000,000行
-販売日時は20111201 ～ 20111210日まで存在して各日時ごとに10万件ずつ存在
-社員番号は各一日に対してUNIQUE、
-部署番号は10個の部署が存在
- TARGET,SALECNTはNOT NULL制約条件を持つカラム
■テーブル生成
CREATE TABLE DECODE_T11
( SALE_DT VARCHAR2(32),---販売日時
EMPNO NUMBER,---社員番号
DEPTNO NUMBER,---部署番号
TARGET NUMBER NOT NULL,---目標販売量
SALECNT NUMBER NOT NULL,---実際の販売量
SALE_DESC VARCHAR2(200) ---目標と実際の販売量に対する詳細内容
);
■データ生成
INSERT INTO DECODE_T1 VALUE
SELECT '20111201' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 710 ) ,
MOD( LEVEL , 400 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,710) || ' ==> ' ||MOD ( LEVEL , 400 )
FROM DUAL
CONNECT BY LEVEL <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111202' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 300 ) ,
MOD( LEVEL , 100 ) ,
'SALE ' || LEVEL || ': '||MOD(LEVEL,300) || ' ==> ' ||MOD ( LEVEL , 100 )
FROM DUAL
CONNECT BY LEVEL+1128 <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111203' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 128 ) ,
MOD( LEVEL , 98 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,128) || ' ==> ' ||MOD ( LEVEL , 98 )
FROM DUAL
CONNECT BY LEVEL+528 <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111204' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 238 ) ,
MOD( LEVEL , 900 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,238) || ' ==> ' ||MOD ( LEVEL , 900 )
FROM DUAL
CONNECT BY LEVEL+278 <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111205' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 897 ) ,
MOD( LEVEL , 1258 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,897) || ' ==> ' ||MOD ( LEVEL , 1258 )
FROM DUAL
CONNECT BY LEVEL+278 <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111206' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 70 ) ,
MOD( LEVEL , 40 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,70) || ' ==> ' ||MOD ( LEVEL , 40 )
FROM DUAL
CONNECT BY LEVEL+134 <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111207' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 8548 ) ,
MOD( LEVEL , 500 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,8548) || ' ==> ' ||MOD ( LEVEL , 500 )
FROM DUAL
CONNECT BY LEVEL+38119 <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111208' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 89 ) ,
MOD( LEVEL , 879 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,89) || ' ==> ' ||MOD ( LEVEL , 879 )
FROM DUAL
CONNECT BY LEVEL+969 <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111209' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 555 ) ,
MOD( LEVEL , 5555 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,555) || ' ==> ' ||MOD ( LEVEL , 5555 )
FROM DUAL
CONNECT BY LEVEL+987 <= 100000;
INSERT INTO DECODE_T1 VALUE
SELECT '20111210' ,
LEVEL ,
MOD( LEVEL , 10 ) ,
MOD( LEVEL , 789 ) ,
MOD( LEVEL , 8792 ) ,
'SALE ' || LEVEL || ' : '||MOD(LEVEL,789) || ' ==> ' ||MOD ( LEVEL , 8792 )
FROM DUAL
CONNECT BY LEVEL+215 <= 100000;
COMMIT;
■インデックス生成および統計情報収集
CREATE INDEX IDX_DECODE_T1_01 ON DECODE_T1(SALE_DT);
CREATE INDEX IDX_DECODE_T1_02 ON DECODE_T1(EMPNO);
EXEC dbms_stats.gather_table_stats('SCOTT', 'DECODE_T1') ;
毎日の総売上目標量と総売上量を確認するSQLを作成するとき、行をカラムで表現するための最も簡単な方法は、スカラーサブクエリを使用することです。 SQLは以下のように書くことができます。
[SQL-1]
SELECT '2011/12/01' saledt_1201,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111201') AS target_1201,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111201') AS sale_1201,
'2011/12/02' saledt_1202,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111202') AS target_1202,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111202') AS sale_1202,
'2011/12/03' saledt_1203,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111203') AS target_1203,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111203') AS sale_1203,
'2011/12/04' saledt_1204,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111204') AS target_1204,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111204') AS sale_1204,
'2011/12/05' saledt_1205,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111205') AS target_1205,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111205') AS sale_1205,
'2011/12/06' saledt_1206,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111206') AS target_1206,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111206') AS sale_1206,
'2011/12/07' saledt_1207,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111207') AS target_1207,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111207') AS sale_1207,
'2011/12/08' saledt_1208,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111208') AS target_1208,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111208') AS sale_1208,
'2011/12/09' saledt_1209,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111209') AS target_1209,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111209') AS sale_1209,
'2011/12/10' saledt_1210,
(SELECT SUM(target) FROM decode_t1 WHERE sale_dt='20111210') AS target_1210,
(SELECT SUM(salecnt) FROM decode_t1 WHERE sale_dt ='20111210') AS sale_1210
from DUAL;
[SQL-1]に対するトレースを実行した後、性能情報を確認すると以下のとおりとなります。
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.68 1.69 0 19744 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.68 1.69 0 19744 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1040 pr=0 pw=0 time=85366 us)
100000 TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=1040 pr=0 pw=0 time=50 us)
100000 INDEX RANGE SCAN IDX_DECODE_T1 (cr=281 pr=0 pw=0 time=6194 us)
1 SORT AGGREGATE (cr=1040 pr=0 pw=0 time=85824 us)
100000 TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=1040 pr=0 pw=0 time=31 us)
100000 INDEX RANGE SCAN IDX_DECODE_T1 (cr=281 pr=0 pw=0 time=6441 us)
1 SORT AGGREGATE (cr=1002 pr=0 pw=0 time=101328 us)
........ --中間省略. --
1 SORT AGGREGATE (cr=1058 pr=0 pw=0 time=88706 us)
99785 TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=1058 pr=0 pw=0 time=30 us)
99785 INDEX RANGE SCAN IDX_DECODE_T1 (cr=281 pr=0 pw=0 time=6535 us)
FAST DUAL (cr=0 pr=0 pw=0 time=1 us)
このように行を列に変えて表現したい場合、DECODEをうまく活用してSQLを作成すれば、テーブルを一度だけ読み込んでも必要な値を取得できるので、最小限の量でデータを抽出することができます。 Oracle 11gに新しく導入された機能のうち、Pivot機能があり、上記のような形のSQL作成がさらに便利になりました。 しかし、今はDECODEを利用した方法を紹介することが目的であり、Oracle 11g以下のバージョンを使用する場合は該当事項がないので、ここで直接取り上げないこととします。
７.１.４.２ 行をカラムに変換時DECODEを活用したSQL作成法
以下は、[SQL-1]がテーブルを繰り返し探索する問題を改善するためにDECODEを活用して作成したSQLで、スカラーサブクエリで作成した部分をDECODE関数を使用してテーブルを一度だけ読み、処理するように導いたものです。
[SQL-2]
SELECT '2011/12/01' saledt_1201,
SUM(DECODE(sale_dt, '20111201',target, 0)) AS target_1201,
SUM(DECODE(sale_dt, '20111201',salecnt, 0)) AS sale_1201,
'2011/12/02' saledt_1202,
SUM(DECODE(sale_dt, '20111202',target, 0)) AS target_1202,
SUM(DECODE(sale_dt, '20111202',salecnt, 0)) AS sale_1202,
'2011/12/03' saledt_1203,
SUM(DECODE(sale_dt, '20111203',target, 0)) AS target_1203,
SUM(DECODE(sale_dt, '20111203',salecnt, 0)) AS sale_1203,
'2011/12/04' saledt_1204,
SUM(DECODE(sale_dt, '20111204',target, 0)) AS target_1204,
SUM(DECODE(sale_dt, '20111204',salecnt, 0)) AS sale_1204,
'2011/12/05' saledt_1205,
SUM(DECODE(sale_dt, '20111205',target, 0)) AS target_1205,
SUM(DECODE(sale_dt, '20111205',salecnt, 0)) AS sale_1205,
'2011/12/06' saledt_1206,
SUM(DECODE(sale_dt, '20111206',target, 0)) AS target_1206,
SUM(DECODE(sale_dt, '20111206',salecnt, 0)) AS sale_1206,
'2011/12/07' saledt_1207,
SUM(DECODE(sale_dt, '20111207',target, 0)) AS target_1207,
SUM(DECODE(sale_dt, '20111207',salecnt, 0)) AS sale_1207,
'2011/12/08' saledt_1208,
SUM(DECODE(sale_dt, '20111208',target, 0)) AS target_1208,
SUM(DECODE(sale_dt, '20111208',salecnt, 0)) AS sale_1208,
'2011/12/09' saledt_1209,
SUM(DECODE(sale_dt, '20111209',target, 0)) AS target_1209,
SUM(DECODE(sale_dt, '20111209',salecnt, 0)) AS sale_1209,
'2011/12/10' saledt_1210,
SUM(DECODE(sale_dt, '20111210',target, 0)) AS target_1210,
SUM(DECODE(sale_dt, '20111210',salecnt, 0)) AS sale_1210
FROM DECODE_T1
WHERE SALE_DT BETWEEN ‘20111201’ AND ‘20111210’;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.85 1.87 0 7184 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.87 1.88 0 7185 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7184 pr=0 pw=0 time=1878579 us)
957364 TABLE ACCESS FULL DECODE_T1 (cr=7184 pr=0 pw=0 time=61 us)
上記SQLでスカラーサブクエリを置き換えるためにDECODEを使用しましたが、変更されたSQLにはあまりにも多くのDECODEを実行することによる非効率性があります。 これらのSQLのパフォーマンスを向上させるためには、DECODE_T1テーブルに対してSALE_DT（20111201〜20111210）ごとにSUM（）を実行してから10件のデータのみを抽出し、DECODEを実行するように変更しなければなりません。
７.１.４.３ 行をカラムに変換するときに DECODE を活用した SQL 作成法が悪いのではないか。?
[SQL-1]に対する改善案を導出する際、DECODEを使用して処理する場合、最小の仕事量で処理が可能であるように見えました。 しかし、DECODEを使用するSQLに変更すると、I/Oは減少しましたが、Elapsed Timeはむしろ増加しました。 結果だけを見れば、I/Oは減少したのですがCPU Timeは増加したので、DECODEを使うのがむしろ性能上不利なものではないかという疑問が挙げられます。 しかし、2つの理由でDECODEで作成した方法の性能が不利に見えるだけなのです。
１．DECODEを使用しなかったSQLの場合、使用するIndex Clustering Factor（注）が良いので、テーブルを繰り返し読み込んだときに発生する非効率が多く減ったという点とデータブロックがともにキャッシュになった状態なのでDisk I/Oが全くなかったという ポイントです。
（注）Index Clustering Factor：テーブルにデータが積載されている順序がインデックスの並べ替えられた順序とどれくらい一致するかを示す数値である。 インデックスとテーブルの並べ替え順序が非常に良い場合、Index Clustering factorはBlock数とほぼ一致し、最も最悪の場合はテーブルの総件数とほぼ一致することになる。 Index Clustering Factorが良いほど、範囲が広いIndex Range Scanを行う場合、Index Lookupを通じて発生するI/O量が大きく減る。
２．DECODEを使ったSQLの場合Select節に抽出されたデータによって、DECODEを繰り返し実行する回数が多いので処理時間が長かったという点です。
もしそうなら、[SQL-2]と比較してCPU Timeが低い[SQL-1]の性能は、Index Clustering Factorによって実際に影響を受けたかどうかを判断するために、強制的に不利に変更した後、[SQL-1]と[SQL-2 ]の性能を比較してみましょう。
[SQL-1]でテストを行ったテーブルと同じデータ件数を持つDECODE_TEMPテーブルを生成し、Where句の条件列であるSALE_DTにインデックスを生成するようにします。 ただし、[SQL-1]とは異なり、Index Clustering Factorを不利にするために、DECODE_TEMPテーブルにデータを入力するときにSALE_DTインデックスとテーブルのデータ順序が一致しないように、TARGET列でソートしてDECODE_TEMPテーブルにデータを入力するようにします。
Index Clustering Factorを不利にするためにTARGETカラムで整列してデータ入力およびインデックス生成
CREATE TABLE decode_temp AS SELECT * FROM decode_t1 ORDER BY target;
CREATE INDEX idx_decode_temp ON decode_temp (sale_dt);
CREATE INDEX idx_decode_temp_02 ON decode_temp (empno);
計情報収集およびDisk I/Oが発生する可能性があるようにBuffer Cache Flush実行
EXEC dbms_stats.gather_table_stats('scott', 'decode_temp');
alter system flush buffer_cache;
[SQL-1]と[SQL-2]のSQLを再実行した結果は下記のとおりです。
[SQL-1] DECODE使わない場合(DECODE_TEMP)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 3.02 37.27 9932 32720 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 3.05 37.31 9932 32720 0 1
[SQL-2] DECODEを使った場合(DECODE_TEMP)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.09 3.27 7175 7184 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.09 3.28 7175 7184 0 1
[SQL-1]、[SQL-2]のトレース結果を見ると、使用するIndex Clustering Factorが悪くなり、DECODEを使用した場合がそうでない場合に比べてI/Oと実行時間が大幅に減少したことが分かります。 さらに、以前のテストでは、むしろ高く出てきたCPU Timeまでも減少したことから、DECODEを使用した場合が性能上はるかに有利であることが分かります。
[SQL-2]のようにDECODEを利用して、性能を改善する事例はすでに広く使用されており、性能上有利な場合が多くあります。 しかし、DECODEをまるで公式のように無条件に使用するのは危険です。 SQLの抽出結果やIndex Clustering Factorなどに対する考慮のない使用は、むしろ性能を低下させることがあるからなのです。
７.１.４.４ DECODE使用時に発生する非効率
DECODEは、SQLがIF、ELSE IF、ELSEなどのロジックを簡単に実装できるようにします。 そして、ローデータをカラムで表現する場合、スカラーサブクエリを使用して繰り返しデータにアクセスして発生する非効率を改善するための方法として使用できるとしてお話してきました。
このようにDECODEは非常に便利ですが、構文がどのように処理されるかを正確に理解することなく使用する場合、むしろDECODEを使用することで非効率が発生する可能性があります。 したがって、DECODEがどのように処理されるかを見て、効率的に使用できる方法について学びましょう。
以下のSQLを見るとDECODE使用回数が多く、DECODE使用時のDefault値を指定しています。 そしてDECODEを実行した後、抽出されたデータにSUM関数を実行しています。
単純に見ると問題はないように見えますが、以下のSQLで使用したDECODE関数には非効率が存在します。 何が問題なのかSQLを見てみましょう。
SELECT '2011/12/01' saledt_1201,
SUM(DECODE(sale_dt, '20111201',target, 0)) AS target_1201,
SUM(DECODE(sale_dt, '20111201',salecnt, 0)) AS sale_1201,
'2011/12/02' saledt_1202,
SUM(DECODE(sale_dt, '20111202',target, 0)) AS target_1202,
SUM(DECODE(sale_dt, '20111202',salecnt, 0)) AS sale_1202,
'2011/12/03' saledt_1203,
SUM(DECODE(sale_dt, '20111203',target, 0)) AS target_1203,
SUM(DECODE(sale_dt, '20111203',salecnt, 0)) AS sale_1203,
'2011/12/04' saledt_1204,
SUM(DECODE(sale_dt, '20111204',target, 0)) AS target_1204,
SUM(DECODE(sale_dt, '20111204',salecnt, 0)) AS sale_1204,
'2011/12/05' saledt_1205,
SUM(DECODE(sale_dt, '20111205',target, 0)) AS target_1205,
SUM(DECODE(sale_dt, '20111205',salecnt, 0)) AS sale_1205,
'2011/12/06' saledt_1206,
SUM(DECODE(sale_dt, '20111206',target, 0)) AS target_1206,
SUM(DECODE(sale_dt, '20111206',salecnt, 0)) AS sale_1206,
'2011/12/07' saledt_1207,
SUM(DECODE(sale_dt, '20111207',target, 0)) AS target_1207,
SUM(DECODE(sale_dt, '20111207',salecnt, 0)) AS sale_1207,
'2011/12/08' saledt_1208,
SUM(DECODE(sale_dt, '20111208',target, 0)) AS target_1208,
SUM(DECODE(sale_dt, '20111208',salecnt, 0)) AS sale_1208,
'2011/12/09' saledt_1209,
SUM(DECODE(sale_dt, '20111209',target, 0)) AS target_1209,
SUM(DECODE(sale_dt, '20111209',salecnt, 0)) AS sale_1209,
'2011/12/10' saledt_1210,
SUM(DECODE(sale_dt, '20111210',target, 0)) AS target_1210,
SUM(DECODE(sale_dt, '20111210',salecnt, 0)) AS sale_1210
FROM DECODE_T1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.85 1.87 0 7184 0 1
------- ------ -------- ---------- ---------- ---------- --------- ----------
total 4 1.87 1.88 0 7185 0 1
理由は、DECODEを使用するときに必ずしも必要ではないDefault値を指定したためです。
Default値を指定して発生した非効率については、以下で詳しく説明するようにすることとし、まず私たちがよく犯す可能性のある間違いについて先に述べたいと思います。 それは、DECODEは比較結果がすべてFALSEであればNULLを返しますが、NULLを返すとSQLの結果値が間違ってしまう恐れがあるため、Default値を指定して使用するというものです。
上記のSQLケースを見ると、DECODEを実行した後にSUM関数を実行しています。 SUM関数の処理時にNULLをどのように処理するかを知っていたなら、DECODEを使用するときにDefault値を指定しなかったでしょう。 もしそうなら、SUM関数でNULLをどのように処理するのかを見てみましょう。
SUM関数の操作には、次の2種類があります。
（１）SUM 関数で数値 + NULL を処理する場合、NULL だけを返します。
SELECT NVL(TO_CHAR(SUM( 1 + NULL )),'ISNULL') FROM DUAL ;
NVL(TO_CHAR(SUM(1+NULL)),'ISNULL')
----------------------------------
ISNULL
（２）NULL データを持つ列の複数行を SUM する場合は、NULL データを除く残りのデータに対してのみ演算処理を行います。
WITH T1 AS (
SELECT /*+ MATERIALIZE */
1 AS NO
FROM DUAL
UNION ALL
SELECT 2 AS NO
FROM DUAL
UNION ALL
SELECT NULL
FROM DUAL
)
SELECT SUM(NO) FROM T1 ;
SUM(NO)
----------
3
もし、SUM関数を実行したカラムに一つでもNULLでない値が存在するならば、最終結果はNULLでありません。 もし、SUM関数を実行したカラムの全てがNULLならばSUM関数の処理結果がNULLになり得ます。 このような場合のためにSUM関数で処理した後にNVL関数を利用して、NULLデータを処理してください。
もし、SUM関数を実行するカラムがNOT NULL制約条件があったり、カラムにDefault値が設定されているならば、NULLデータが存在しないために、SUM関数にNVL関数を利用してNULLデータを処理することは不必要な作業となります。
それでは、DECODEに不必要なDefault値を指定した場合、性能にどんな影響を及ぼすのかに対して調べてみることにしましょう。
上記でDefault値を指定したSQLを分析し続けます。 DECODE 関数の比較列として使われた SALE_DT は、Select 節で DECODE と比較される日付 (定数部分: 20111201 ～ 20111210) をすべて持っており、TARGET や SALECNT は NOT NULL 制約条件のあるカラムであるため、NULL 抽出は基本的に不可能となります。 そのため、Default値を設定しなくても構いません。 したがって、上記SQLで不必要なデフォルト値を除去したSQLを実行してみました。
SELECT '2011/12/01' saledt_1201,
SUM(DECODE(sale_dt, '20111201',target)) AS target_1201,
SUM(DECODE(sale_dt, '20111201',salecnt)) AS sale_1201,
'2011/12/02' saledt_1202,
SUM(DECODE(sale_dt, '20111202',target)) AS target_1202,
SUM(DECODE(sale_dt, '20111202',salecnt)) AS sale_1202,
'2011/12/03' saledt_1203,
SUM(DECODE(sale_dt, '20111203',target)) AS target_1203,
SUM(DECODE(sale_dt, '20111203',salecnt)) AS sale_1203,
'2011/12/04' saledt_1204,
SUM(DECODE(sale_dt, '20111204',target)) AS target_1204,
SUM(DECODE(sale_dt, '20111204',salecnt)) AS sale_1204,
'2011/12/05' saledt_1205,
SUM(DECODE(sale_dt, '20111205',target)) AS target_1205,
SUM(DECODE(sale_dt, '20111205',salecnt)) AS sale_1205,
'2011/12/06' saledt_1206,
SUM(DECODE(sale_dt, '20111206',target)) AS target_1206,
SUM(DECODE(sale_dt, '20111206',salecnt)) AS sale_1206,
'2011/12/07' saledt_1207,
SUM(DECODE(sale_dt, '20111207',target)) AS target_1207,
SUM(DECODE(sale_dt, '20111207',salecnt)) AS sale_1207,
'2011/12/08' saledt_1208,
SUM(DECODE(sale_dt, '20111208',target)) AS target_1208,
SUM(DECODE(sale_dt, '20111208',salecnt)) AS sale_1208,
'2011/12/09' saledt_1209,
SUM(DECODE(sale_dt, '20111209',target)) AS target_1209,
SUM(DECODE(sale_dt, '20111209',salecnt)) AS sale_1209,
'2011/12/10' saledt_1210,
SUM(DECODE(sale_dt, '20111210',target)) AS target_1210,
SUM(DECODE(sale_dt, '20111210',salecnt)) AS sale_1210
FROM DECODE_T1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.17 1.17 0 7184 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.17 1.17 0 7184 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7184 pr=0 pw=0 time=1177186 us)
957364 TABLE ACCESS FULL DECODE_T1 (cr=7184 pr=0 pw=0 time=55 us)
Default値を指定した場合CPU Timeが1.8秒以上ありましたが、これを削除すると1.2秒となり性能の改善が見られます。
このような速度差の理由は何なのでしょうか？。その理由はDECODEをIF文に変えてみればわかります。 Default値が指定されたDECODEをIF文に変更したSQL文は下記のとおりです。
IF条件式THEN
RETURN値
ELSE
RETURN値
END IF
反面、Default値がないDECODEをIF文で表現してると以下のとおりとなります。
IF条件式THEN
RETURN値
END IF
Default値が指定されれば、追加でELSEロジックをさらに処理するということが分かります。 すなわち、演算が追加されたことからCPUをさらに長く占有するのは当然のことなのです。
DECODEにDefault値を指定したSQLの最終抽出結果が多かったり、実行頻度が多いならばその性能差はより一層大きくなるので、0.6秒差だけであるとこれを軽視してはいけません。
しかし、性能向上のためにDefault値を無条件除去するのはデータ整合性の側面で問題になることがあります。 前の例題ではDECODEで比較対象になるSALE_DT値がテーブルに全部値があることをを知っていたこと、比較後値をリターン値がNOT NULL制約条件によりNULLが抽出されることができないので、Default値を除去することができたのです。
しかし、DECODEでSALE_DTと比較する日が変更されて、この時一致するデータが存在しなかったり、TARGET,SALECNTカラムにNOT NULL制約条件がないならば、NULLを抽出し、この値を持ってまた他の演算をする場合、誤った結果が照会されるためにDefault値を除去するのが難しくなります。 そこでNVL関数をうまく活用すればDefault値を除去することができるのです。
Default値を除去してNVLを使った結果は以下のとおりです。
SELECT '2011/12/01' saledt_1201,
NVL(SUM(DECODE(sale_dt, '20111201',target)),0) AS target_1201,
NVL(SUM(DECODE(sale_dt, '20111201',salecnt)),0) AS sale_1201,
'2011/12/02' saledt_1202,
NVL(SUM(DECODE(sale_dt, '20111202',target)),0) AS target_1202,
NVL(SUM(DECODE(sale_dt, '20111202',salecnt)),0) AS sale_1202,
'2011/12/03' saledt_1203,
NVL(SUM(DECODE(sale_dt, '20111203',target)),0) AS target_1203,
NVL(SUM(DECODE(sale_dt, '20111203',salecnt)),0) AS sale_1203,
'2011/12/04' saledt_1204,
NVL(SUM(DECODE(sale_dt, '20111204',target)),0) AS target_1204,
NVL(SUM(DECODE(sale_dt, '20111204',salecnt)),0) AS sale_1204,
'2011/12/05' saledt_1205,
NVL(SUM(DECODE(sale_dt, '20111205',target)),0) AS target_1205,
NVL(SUM(DECODE(sale_dt, '20111205',salecnt)),0) AS sale_1205,
'2011/12/06' saledt_1206,
NVL(SUM(DECODE(sale_dt, '20111206',target)),0) AS target_1206,
NVL(SUM(DECODE(sale_dt, '20111206',salecnt)),0) AS sale_1206,
'2011/12/07' saledt_1207,
NVL(SUM(DECODE(sale_dt, '20111207',target)),0) AS target_1207,
NVL(SUM(DECODE(sale_dt, '20111207',salecnt)),0) AS sale_1207,
'2011/12/08' saledt_1208,
NVL(SUM(DECODE(sale_dt, '20111208',target)),0) AS target_1208,
NVL(SUM(DECODE(sale_dt, '20111208',salecnt)),0) AS sale_1208,
'2011/12/09' saledt_1209,
NVL(SUM(DECODE(sale_dt, '20111209',target)),0) AS target_1209,
NVL(SUM(DECODE(sale_dt, '20111209',salecnt)),0) AS sale_1209,
'2011/12/10' saledt_1210,
NVL(SUM(DECODE(sale_dt, '20111210',target)),0) AS target_1210,
NVL(SUM(DECODE(sale_dt, '20111210',salecnt)),0) AS sale_1210
FROM DECODE_T1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.15 1.14 0 7184 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.17 1.15 0 7185 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=7184 pr=0 pw=0 time=1147532 us)
957364 TABLE ACCESS FULL DECODE_T1 (cr=7184 pr=0 pw=0 time=55 us)
NVL 関数を追加する際、関数の実行回数が最も少ない位置に追加しなければ効率的な実行はできないのです。
- NVL(SUM(DECODE(SALE_DT,’20111204′,TARGET)),0) → 効率が良い
- SUM(DECODE(SALE_DT,’20111204′,NVL(TARGET、0))) → 効率が悪い
７.１.４.５ DECODEの誤った使用でインデックスを使うことができない性能問題と解決方法
７.１.４.５.１ BIND値に伴う実行計画分岐
次のSQLは、:B1がNULLであれば完全に照会し、:B1がNULLでない場合はEMPNOを:B1の値で照会するSQLを作成したものです。
SELECT /*+ NO_EXPAND */
*
FROM DECODE_T1 a
WHERE empno = DECODE(:b1, NULL, a.empno, :b1);
EMPNOは選択性が非常に良い列でインデックスが存在するので、:B1がNULLでなければ、インデックスを使用するのが効率的であり、:B1がNULLなら、条件句がEMPNO=A.EMPNOになり、データ全体を読み取る必要があるので、 Full Table Scanが最も効率的な実行方法になります。 実際のそれぞれのケースを実行し、その結果を見て見ましょう。
まず：B1にNULL以外の値を入力し、トレースを実行してみましょう。
var b1 number
EXEC :b1 := 10
SELECT /*+ NO_EXPAND */ *
FROM DECODE_T1 a
WHERE empno = decode(:b1, NULL, a.empno, :b1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.01 0.13 0 0 0 0
Fetch 2 0.32 0.41 1 7185 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.34 0.57 1 7185 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 TABLE ACCESS FULL DECODE_T1 (cr=7185 pr=1 pw=0 time=417594 us)
:B1に10 (NOT NULL)が入力されたのでインデックス スキャン方式で実行されることが効率的です。 しかし、期待とは違いFull Table Scanで実行して居ます。 もし、該当SQLの90%以上がNULLでないデータで照会されるならば、不必要なFull Table Scanにより非効率が発生します。
したがってこのようなSQLの場合は:B1に入力される値によりインデックス スキャンやFull Table Scanで実行計画を分岐して実行することがSQL性能に最も適合した実行方法となります。 したがってDECODE代わりにUNION ALLを使ってそれぞれの場合に適合した実行計画で実行されるようにしなければなりません。
SELECT * FROM decode_t1 WHERE empno = :b1 AND :b1 IS NOT NULL
UNION ALL
SELECT * FROM decode_t1 WHERE empno = empno AND :b1 IS NULL
上記のSQLを見ると、DECODE_T1テーブルを2回読み込むように見えます。 ただし、UNION ALLの上のWhere句にあるAND：B1 IS NOT NULL条件を先に確認し、TRUEの場合はUNION ALLの上部を行い、FALSEの場合は行いません。 そしてUNION ALLの下端部分も同様に行今います。 したがって、SQLにはDECODE_T1テーブルを2回処理するように見えますが、実際に1回だけ処理するため、非効率が存在しません。
該当SQLの:B1に10を入力した後実行したトレース結果は次のとおりです。
SELECT /*+ INDEX(A IDX_DECODE_T1_02) */ *
FROM decode_t1 a
WHERE empno = :b1
AND :b1 is not null
union all
SELECT /*+ full(b) */ *
FROM decode_t1 b
WHERE empno = empno
AND :b1 IS NULL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 14 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 UNION-ALL (cr=14 pr=0 pw=0 time=171 us)
10 FILTER (cr=14 pr=0 pw=0 time=164 us)
10 TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=14 pr=0 pw=0 time=149 us)
10 INDEX RANGE SCAN IDX_DECODE_T1_02 (cr=4 pr=0 pw=0 time=60 us)
0 FILTER (cr=0 pr=0 pw=0 time=1 us)
0 TABLE ACCESS FULL DECODE_T1 (cr=0 pr=0 pw=0 time=0 us)
：B1がNULLではないため、UNION ALLの一番下のSQLはFALSEになって実行されず、UNION ALLの一番上のSQLはTRUEになり、対応するSQLのみが実行されたことがわかります。 実行計画を見ると、UNION ALL上段・下段のSQLともに実行計画が樹立されており、それぞれ効率的な実行が可能なようにインデックススキャンおよびFull Table Scanで樹立されたことが分かりました。
UNION ALLの下段の部分はFull Table Scanで表記されていますが、CRが0ブロックでこれは実際の実行されはしなかったということを意味します。
このようにWhere節にDECODEを使う場合、SQLは一つだが実際にはBind値により分岐して実行しなければならない場合があるので留意して使わなければなりません。
７.１.４.５.２ DECODEをWhere節に使う時発生する非効率の他の例
Where句にDECODEを使用すると、式に変数や定数値ではなく、テーブルの列がある場合、列に効率的なインデックスが存在してもインデックススキャンを実行できません。実際にこのように作成されることは極めてまれであるが、使用されることがあるならば、効率的な実行計画で行われる確率はかなり低いでしょう。 以下の例で問題が発生する理由を理解し、どのように解決するかを確認しましょう。
SELECT *
FROM decode_t1 a
WHERE empno = DECODE( deptno, :b1, :b1, :b2 );
上記SQLは、:B1値と、DECODE_T1テーブルのDEPTNO値が等しい場合にEMPNO = :B1条件で照会を行い、DECODE_T1テーブルのDEPTNOと：B1値が等しくない場合にはEMPNO = :B2条件で照会を行い、 データを抽出したいSQLです。
該当SQLに対するトレース結果は次のとおりです。
var b1 number
var b2 number
exec :b1 := 10
exec :b2 := 20
select *
FROM decode_t1 a
WHERE empno = DECODE( deptno, :b1, :b1, :b2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.09 0 0 0 0
Execute 1 0.01 0.25 0 0 0 0
Fetch 2 0.21 4.43 0 7185 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.23 4.78 0 7185 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 TABLE ACCESS FULL DECODE_T1 (cr=7185 pr=0 pw=0 time=4432862 us)
MPNOカラムはインデックスを使うことが効率的です。 しかし、実際実行してみると、インデックスを使わないでFull Table Scanを実行しています。 ひょっとしてインデックスを使うようにヒントを与えればインデックス スキャンをするのでしょうか？
インデックスを使うようにヒントを追加した後実行してみましょう。
var b1 number
var b2 number
exec :b1 := 10
exec :b2 := 20
SELECT /*+ INDEX(A IDX_DECODE_T1_02) */
*
FROM DECODE_T1 a
WHERE empno = DECODE( deptno, :b1, :b1, :b2 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 2 1.77 2.36 0 959354 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.79 2.37 0 959354 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=959354 pr=0 time=2365908 us)
957364 INDEX FULL SCAN IDX_DECODE_T1_02 (cr=2122 pr=0 pw=0 time=86603 us)
しかしIndex Range ScanではなくIndex Full Scanで実行されています。 そしてEMPNO =:B1あるいはEMPNO =:B2条件はアクセス条件で使われることができなくて、単純にFilterで使われてFull Table Scanより大きい非効率が発生しました。 上記SQLの性能改善はEMPNO =:B1あるいはEMPNO =:B2の条件をどのようにインデックスで処理するかに決定されます。
EMPNO条件で照会をする場合、インデックスを利用すればデータを非常に効率的に抽出することができます。 したがってEMPNO =:B1条件でデータを抽出した集合とEMPNO =:B2条件で抽出した集合をUNION ALLと合わせ、EMPNO = DECODE( DEPTNO,:B1,:B1,:B2 )はFilterで処理するようにすれば改善になると考えられます。 下記のSQLは改善内容を適用して作成したSQLです。 トレースを実行して性能が改善されたのか確認してみることにしましょう。
SELECT * FROM DECODE_T1 WHERE empno = DECODE( deptno, :b1, :b1, :b2 ) AND empno=:b1
union all
SELECT * FROM DECODE_T1 WHERE empno = DECODE( deptno, :b1, :b1, :b2 ) AND empno=:b2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 2 0.00 0.00 0 27 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 27 0 10
Rows Row Source Operation
------- ---------------------------------------------------
10 UNION-ALL (cr=27 pr=0 pw=0 time=211 us)
0 TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=13 pr=0 pw=0 time=135 us)
10 INDEX RANGE SCAN IDX_DECODE_T1_02 (cr=3 pr=0 pw=0 time=58 us)
10 TABLE ACCESS BY INDEX ROWID DECODE_T1 (cr=14 pr=0 pw=0 time=66 us)
10 INDEX RANGE SCAN IDX_DECODE_T1_02 (cr=4 pr=0 pw=0 time=16 us)
上記変更されたSQLの内容のうち太字の条件をUNION ALL上段・下段のSQLにそれぞれ追加してインデックス使用が可能に誘導した後、既存のDECODEが使用された条件はFilter条件として使用させて望むのデータを効率的に抽出しました。
今回は、これで終了です。次回は、「DECODE & CASE WHENの理解および条件ステートメントの処理」の二回目「CASE」についてです。ご期待ください。それでは、See you ^^
