2022.09.21
SQLチューニング（第36回）「任意のデータを活用した効率的なSQLの作成」（2／2回）
今回は、「任意のデータを活用した効率的なSQLの作成」二回目になります。早速、始めましょう。
9.２.1 データ変換時CONNECT BY LEVELを使う
以下のSQLを実行すると、抽出結果は1行で表されます。 しかし、読みやすさを高めるために横列を縦列に変換して抽出したい場合があるでしょう。 このような場合、SQLをどのように書くべきですか？ まず、水平列のデータを抽出するSQLを見てみましょう。
WITH connect_by_level_t
AS (
SELECT '通信会社' AS C1,
'SKテレコム' AS c2,
'KT' AS C3,
'LGU+' AS C4
FROM DUAL
)
SELECT *
FROM connect_by_level_t t ;
C1 C2 C3 C4通信会社
---------- ---------- ---------- -------- -----------
通信会社SKテレコムKT LGU+ SKテレコム
KT
LGU+
上記のSQLで行データ（横列）を列データ（縦列）に置き換えるには、Connet By LevelとDECODEを使用すると処理できます。 SQL作成の鍵は、結果件数だけLEVELを指定しなければならないという点と、結果件数だけDECODEを使用して作成しなければならないということです。 したがって、水平に出力されるデータを縦に出力できるように作成するSQLは、以下のように作成できます。
WITH connect_by_level_t
AS ( SELECT '通信会社' AS C1,
'SKテレコム' AS c2,
'KT' AS C3,
'LGU+' AS C4
FROM DUAL )
SELECT C1 as "通信会社"
FROM (
SELECT tt.rno
、MAX(DECODE(tt.rno、1,DECODE(t.c1,'通信会社',DECODE(t.c2,'SKテレコム',t.c2))
、2,DECODE(t.c1,'通信会社',DECODE(t.c3,'KT',t.c3))
、3,DECODE(t.c1,'通信会社',DECODE(t.c4,'LGU+',t.c4)))) AS C1
FROM connect_by_level_t t,
( SELECT LEVEL AS rno FROM DUAL CONNECT BY LEVEL <= 3 ) tt
GROUP BY tt.rno ) ;
通信会社
--------
SKテレコム
KT
LGU+
9.2.２ データ重複照会の除去
今回は任意のデータを生成して、同一データに対する重複照会を除去する方法に対して調べてみるようにします。 テストに先立ち先にテスト スクリプトを実行することにしましょう。
Script. テスト データ生成用
DROP TABLE CONNECT_BY_LEVEL_SUM PURGE ;
< CONNECT_BY_LEVEL_SUM >
■テーブル生成
CREATE TABLE CONNECT_BY_LEVEL_SUM
AS
SELECT LEVEL as c1, to_char(to_date('19940101','yyyymmdd') + level-1,'yyyymmdd') as c2
FROM DUAL
CONNECT BY LEVEL <= 100000 ;
以下のSQL[1]は、月(系)と日(系)データをUNION ALLを用いて、同一データを2回読み込み、UNION ALLの上段には月(系)を、下段には日(系)を抽出する SQLです。 ここで問題となる部分は、同じデータを2回読み取らなければならないという点です。
9.2.2.1 SQL[1]. 同一データ重複照会による非効率発生SQL
SELECT '月(系)' AS c1,
SUBSTR(c2,1,6) AS c2,
SUM(c1) AS c3
FROM connect_by_level_sum
WHERE c2 BETWEEN TO_CHAR(TRUNC(SYSDATE,'month'),'yyyymmdd')
AND TO_CHAR(SYSDATE,'yyyymmdd')
GROUP BY SUBSTR(c2,1,6)
UNION ALL
SELECT 'こと(系)',
c2,
SUM(c1)
FROM connect_by_level_sum
WHERE c2 BETWEEN TO_CHAR(TRUNC(SYSDATE,'month'),'yyyymmdd')
AND TO_CHAR(SYSDATE,'yyyymmdd')
GROUP BY c2 ;
C1 C2 C3
---------------- ------------------------------------------------ ----------
月(系) 201111 13029
こと(系) 20111101 6514
こと(系) 20111102 6515
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 114 | 130 (53)| 00:00:02 |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 3 | 57 | 65 (5)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| CONNECT_BY_LEVEL_SUM | 3 | 57 | 64 (4)| 00:00:01 |
| 5 | HASH GROUP BY | | 3 | 57 | 65 (5)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL| CONNECT_BY_LEVEL_SUM | 3 | 57 | 64 (4)| 00:00:01 |
--------------------------------------------------------------------------------------------
前述のSQL [1]で同じデータを2回読み取る非効率性を排除することはできませんか？
SQL の 2 つのテーブルが結合すると、結合条件が存在しない場合に Cartesian Product が発生します。 このときデータがＭ＊Ｍに複製されますが、このような点を利用して、元のデータを所望だけ複製し、同じデータを何度も繰り返し読み取ることなく処理することができます。
上記の説明がわかりにくい可能性があるため、Cartesian Productが発生した場合にデータがどのように複製されるかを簡単に調べてみましょう。
テーブルＡにはＸという列のデータが２１、２２、２３存在し、ＢテーブルにはＹ列のデータが１、２存在するとする。 Cartesian Productが発生すると、3×2で合計6件のデータが抽出されます。
テーブルA テーブルB
カラムX カラムY
21 1
22 2
23
Cartesian Productが発生した以後結果データ
引き締めた後結果
カラムX カラムY
21 1
22 1
23 1
21 2
22 2
23 2
結合後の結果を見ると、Ｙ値（Ｂテーブルの値）に基づいてＸ値（Ａテーブルの値）の全体が複製されたことがわかります。 したがって、Ｙ値が１のものについては月（計）を求め、Ｙ値が２のものについては日（計）を求めればテーブルを一度だけ読み込んでも処理できるようになります。
Connet By Levelが存在しない当時は、Cartesian Productを発生させてデータを複製するために別々のCOPYテーブルを作成したり、ディクショナリビューなどを用いて作成しました。 しかし、Oracle 10g以降からはCOPYテーブルの代わりに、以下のようにConnect By Levelを利用すれば追加のI/Oなしで処理できます。
9.2.2.2 SQL[2]. 同一データ重複照会を除去した効率的なSQL
SELECT DECODE(y.rno、1,'こと(系)','月(系)') AS C1
,DECODE(y.rno,1,c2,substr(c2,1,6)) AS C2
,SUM(DECODE(y.rno,1,c3,c3)) AS C3
FROM (
SELECT c2, SUM(c1) AS c3
FROM CONNECT_BY_LEVEL_SUM
WHERE c2 BETWEEN TO_CHAR(TRUNC(SYSDATE,'month'),'yyyymmdd')
AND TO_CHAR(SYSDATE,'yyyymmdd')
GROUP BY c2
) X,
(SELECT LEVEL AS rno FROM DUAL CONNECT BY LEVEL <= 2) Y
GROUP BY DECODE(y.rno、1,'こと(系)','月(系)'),
DECODE(y.rno,1,c2,SUBSTR(c2,1,6)) ;
C1 C2 C3
---------------- ------------------------------------------------ ----------
月(系) 201111 13029
こと(系) 20111102 6515
こと(系) 20111101 6514
----------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Time |
----------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3 | 96 | 00:00:01 |
| 1 | HASH GROUP BY | | 3 | 96 | 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 3 | 96 | 00:00:01 |
| 3 | VIEW | | 1 | 13 | 00:00:01 |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | |
| 5 | FAST DUAL | | 1 | | 00:00:01 |
| 6 | BUFFER SORT | | 3 | 57 | 00:00:01 |
| 7 | VIEW | | 3 | 57 | 00:00:01 |
| 8 | HASH GROUP BY | | 3 | 57 | 00:00:01 |
|* 9 | FILTER | | | | |
|* 10 | TABLE ACCESS FULL | CONNECT_BY_...| 3 | 57 | 00:00:01 |
----------------------------------------------------------------------------------|
9.2.3 インデックス処理の負荷が多い場合
9.2.3.1 任意の数字データ生成後使用する
任意の数字データを生成して、性能を改善したもう一つの事例を通じて問題点を把握して改善案を導き出してみることにしましょう。
[性能問題SQL]
SELECT A.*
FROM tb_slp A
WHERE status IN ('I','T')
AND part_id >= :b0 AND part_id <= :b1
ORDER BY part_id ASC ,
slp_dtti ASC ,
slp_seq ASC ;
[性能問題SQL]が本当非効率が発生するのか、非効率が発生するならばどの程度なのか調べてみるためにV$SQLAREAで実行内訳を問い合わせてみましょう。
[SQL実行内訳[HASH VALUE:450651061]]
SCHEMA EXEC DISK_READS BUFFER_GETS Rows BUFFER GETS(1) ELAPSED(1)
---------- ------ ---------- ----------- ---------- -------------- ----------
OLNAPPB 9318 19288567 1305041823 33311 140056.002 14.1427943
[SQL Bind Values [HASH VALUE : 450651061]]
Bind Name Date Datatype Bind Value
---------- ------------------- ---------- -----------
:B0 2011-07-18 14:57:22 1 0
:B1 2011-07-18 14:57:22 1 24
テーブル性格:パーテーション テーブル
パーテーション キー:part_id
パーテーション キー値:00 ～ 99 (合計100個、パーテーション個数は固定)
インデックス構成情報:TB_SLP_IX1 - part_id,status
[性能問題SQL]はSQL実行内訳のうちROWS(総抽出件数)/EXEC(総実行回数)を計算すれば33,311/9,318 = 3.57件で平均4件未満のデータを抽出したことが分かりました。
もし、インデックスが存在せず非効率が発生したわけではないことを確認するために実行計画を確認してみると、Full Table ScanではなくIndex Range Scanで行われており、特に問題がないように見えます。 しかし、[性能問題SQL]の実行履歴結果を見ると、平均4件未満を抽出するために発生する平均I/O処理量(BUFFER GET(1))が140,056ブロックで、抽出件数に比べて非常に多くなります。
性能問題SQL]の実行内訳と関連情報を分析した結果、非効率はインデックス構成と照会条件との関係による問題であることを分かることができました。
それならなぜこのような非効率が発生したのか調べてみましょう。 Where節のPART_ID >=:B0 AND PART_ID <=:B1条件はパーテーション テーブルのパーテーション範囲を現わすことであり、STATUS IN (‘I’,’T’)はデータの状態をチェックする条件です。
ところがSTATUS条件に対するデータがごく少数であるが、インデックスの先頭カラムであるPART_IDが範囲条件で行われ、STATUS条件がIndex Access predicate条件として使用されず、Index Filter Predicateで行われました。 したがって、少ないデータを抽出するにもかかわらず、大量のI/O処理を行うことになったのです。
まとめると、索引から PART_ID 列を照会条件で範囲検索を行った後、 PART_ID 条件で抽出された多くのデータについて、表アクセスをする前の索引の末尾列にある STATUS 列に対する条件を Index Filter Predicate で行い、 ほぼほとんどのデータをろ過していたのです。
PART_IDカラムに値を付与した後SQLのトレース結果を確認してみることにしましょう。
var b0 varchar2(100)
var b1 varchar2(100)
exec :b0 := '00'
exec :b1 := '99'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 2 82.33 712.21 479960 561444 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 82.34 712.22 479960 561444 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=561444 pr=479960 pw=0 time=712216270 us)
1 FILTER (cr=561444 pr=479960 pw=0 time=712216119 us)
1 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=561444 pr=479960 time=712216112 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID TB_SLP PARTITION:KEY KEY (cr=561444 pr=479960…)
1 INDEX RANGE SCAN TB_SLP_IX1 PARTITION:KEY KEY (cr=561442 pr=479959 …)
上記のようにインデックスの先頭列を範囲照会する場合、インデックス構成列のうちデータを効果的に減少させるSTATUS条件はIndex Filter Predicateで処理することになります。 このように処理され抽出されたデータは１件ですが、Ｉ／Ｏは５６１，４４２ブロックも処理しました。 つまり、インデックスの先頭列であるPART_ID列の条件で抽出された多くのデータでインデックススキャンを行い、抽出件数に比べて多すぎるI/Oを処理するようになったのです。
したがって、SQLのパフォーマンス改善のためには、データを削減できるSTATUS条件がIndex Access Predicateで使用できるようにインデックスの構成を変更したり、範囲検索を行うPART_ID条件に対する照会方法をEQUALやIN方式に変えてパフォーマンスを 改善する方案を考えることができます。
- 改善策 1. インデックス構成情報を変更 ( PART_ID, STATUS STATUS, PART_ID )
- 改善方法 2. インデックスフィルタにならないように範囲検索をINまたは=(EQUAL)照会に変更
改善案1のような方法が可能であれば最も効率的な改善案となるでしょうが、運用業務環境ではインデックス変更が不可能な場合があります。 例えば、PART_ID列だけで照会される場合があり、必然的にPART_IDが先頭列であるインデックスがなければならない場合と、テーブルサイズが大きすぎてインデックスを変更することが負担になる場合などです。
この場合、上記の改善策のうちの2番目の方法を導き出して適用することを検討する必要があります。 PART_IDに対する範囲条件の値を、任意のテーブルにデータを格納した後、それを結合として処理すると、既存のインデックスを使用するより効率的でしょう。 つまり、範囲条件の値をConnect By Levelを使ってデータを生成した後、Where句のPART_IDに対する範囲検索の代わりに結合で処理方式を変えれば、後続のカラムがIndex Access Predicateで行われ、既存の非効率を解消することができます。
例えば、PART_IDの抽出対象データが1〜4までの範囲であれば、Connect By Levelを用いて対応する値1、2、3、4を抽出して結合に変更します。ただし、PART_IDが整数値のみ存在するという仮定が必要です。
それでは[性能問題SQL]の性能を改善してみましょう。
PART_IDの範囲条件値は以下のように生成することができます。
SELECT LPAD((LEVEL-1)+:b0,2,0) part_id
FROM DUAL
CONNECT BY LEVEL <= (:b1-:b0)+1 ;
PART_ID
-------
00
01
02
03
04
05
…省略…
97
98
99
[改善後SQL]は、前述のPART_ID範囲条件値を抽出して結合に変更したSQLです。 SQLのトレース結果を見ると、改善前に比べて多くの性能改善効果があることがわかります。
[改善後SQL]
var b0 varchar2(100)
var b1 varchar2(100)
exec :b0 := '00'
exec :b1 := '99'
SELECT /*+ LEADING(PART_ID) USE_NL(PART_ID A) INDEX(A TB_SLP_IX1) */
A.*
FROM tb_slp A,
( -----> Dummy Join追加
SELECT LPAD((LEVEL-1)+:b0,2,0) part_id
FROM DUAL
CONNECT BY LEVEL <= (:b1-:b0)+1
) part_id
WHERE a.status IN ('I','T')
AND a.part_id >= :b0
AND a.part_id <= :b1
AND part_id.part_id = a.part_id -----> Dummy Tableを利用した結合条件追加
ORDER BY part_id ASC,
slp_dtti ASC,
slp_seq ASC ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 2 0.03 0.40 204 712 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.41 204 712 0 1
Rows Row Source Operation
----- ---------------------------------------------------
1 SORT ORDER BY (cr=712 pr=204 pw=0 time=401047 us)
1 FILTER (cr=712 pr=204 pw=0 time=400939 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID TB_SLP (cr=712 pr=204 pw=0 time=400929 us)
102 NESTED LOOPS (cr=711 pr=203 pw=0 time=883868 us)
100 VIEW (cr=0 pr=0 pw=0 time=3237 us)
100 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=40 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us)
1 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=711 pr=203 pw=0 time=383495)
1 INLIST ITERATOR (cr=711 pr=203 pw=0 time=382716 us)
1 INDEX RANGE SCAN TB_SLP_IX1 PARTITION:KEY KEY (cr=711 pr=203 …)
9.3.2.2 任意の日データ生成後使用する
前述のConnect By Levelを使用して任意の数を作成し、SQLのパフォーマンスを向上させる方法について学びました。 今回は、ランダムな日付データを生成してパフォーマンスを向上させる方法を学びましょう。 テストする前に、まずテストスクリプトを最初に実行しましょう。
Script. テスト データ生成用
DROP TABLE DUMMY_TABLE_DATE PURGE;
< DUMMY_TABLE_DATE >
■テーブル生成
CREATE TABLE DUMMY_TABLE_DATE (
c1 number,
c2 char(1),
c3 varchar2(8)
) ;
alter session set nls_date_format='yyyymmdd' ;
■データ生成
INSERT INTO DUMMY_TABLE_DATE
SELECT LEVEL,
CHR(65+mod(LEVEL,26)),
DECODE(MOD(LEVEL,5),0,'20110101',
1,'20110102',
2,'20110103',
3,'20110104',
4,'20110105')
from DUAL
CONNECT BY LEVEL <= 500000 ;
COMMIT ;
■各カラムにインデックス生成および統計情報収集
CREATE INDEX DUMMY_TABLE_DATE_IDX01 ON DUMMY_TABLE_DATE (c3,c2,c1) ;
[比較テスト(BETWEEN vs. CONNECT BY LEVEL活用)]
SELECT c3, COUNT(*)
FROM DUMMY_TABLE_DATE
GROUP BY c3
ORDER BY c3 ;
C3 COUNT(*)
---------------- ----------
20110101 100000
20110102 100000
20110103 100000
20110104 100000
20110105 100000
上記のSQLの結果値は、C3列のデータ分布図を意味します。 つまり、C3値の種類は20110101〜20110105までの5つで、それぞれ100,000件ずつ存在します。 そして、DUMMY_ ABLE_DATEテーブルのインデックスには、C3 + C2の順になった結合インデックスがあります。 次に、DUMMY_TABLE_ DATEテーブルにインデックスの先頭列であるC3列をBETWEENで照会するときとConnect By Levelを活用して結合として処理する場合のパフォーマンスの違いを比較してみましょう。
テスト[1]． BETWEENビュー
var b1 varchar2(10)
var b2 varchar2(10)
var b3 varchar2(10)
exec :b1 := '20110101'
exec :b2 := '20110105'
exec :b3 := 'A'
SELECT /*+ INDEX(X DUMMY_TABLE_DATE_IDX01) */
X.*
FROM DUMMY_TABLE_DATE X
WHERE x.c3 BETWEEN :b1 AND :b2
AND x.c2 = :b3
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 2 0 0
Fetch 1283 0.06 0.07 0 2797 0 19230
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1285 0.06 0.07 0 2799 0 19230
Rows Row Source Operation
------- ---------------------------------------------------
19230 FILTER (cr=2797 pr=0 pw=0 time=38504 us)
19230 INDEX RANGE SCAN DUMMY_TABLE_DATE_IDX01 (cr=2797 pr=0 pw=0 time=19261 us)
前述の任意の数を生成してパフォーマンスを改善した[パフォーマンス問題SQL]と同様に、テスト[1]もインデックス先頭列C3の条件をBETWEENで比較するよりも結合で処理すればパフォーマンスを改善することができるでしょう。 そのためには、まずConnect By Levelを使用して任意の日付データを次のように生成する必要があります。
SELECT TO_CHAR(TO_DATE(:b1,'yyyymmdd')+LEVEL-1,'yyyymmdd') AS c3
FROM DUAL
CONNECT BY LEVEL <= (TO_DATE(:b2,'yyyymmdd')-TO_DATE(:b1,'yyyymmdd')+1) ;
C3
--------
20110101
20110102
20110103
20110104
20110105
任意の日データを生成して、これを以下のテスト[2]とともに結合で処理するならば性能を改善することができます。
テスト[2]. CONNECT BY LEVELを活用した照会
var b1 varchar2(10)
var b2 varchar2(10)
var b3 varchar2(10)
exec :b1 := '20110101'
exec :b2 := '20110105'
exec :b3 := 'A'
SELECT /*+ LEADING(D) USE_NL(D X) INDEX(X DUMMY_TABLE_DATE_IDX01) */
X.*
FROM DUMMY_TABLE_DATE X,
( ---> Join追加
SELECT TO_CHAR(TO_DATE(:b1,'yyyymmdd')+LEVEL-1,'yyyymmdd') AS c3
FROM DUAL
CONNECT BY LEVEL <= (TO_DATE(:b2,'yyyymmdd')-TO_DATE(:b1,'yyyymmdd')+1)
) d
WHERE d.c3 = x.c3 --->結合条件追加(BETWEENを結合に変更)
AND x.c2 = :b3
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 2 0 0
Fetch 1283 0.03 0.02 0 1364 0 19230
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1285 0.03 0.02 0 1366 0 19230
Rows Row Source Operation
------- ---------------------------------------------------
19230 NESTED LOOPS (cr=1364 pr=0 pw=0 time=38538 us)
5 VIEW (cr=0 pr=0 pw=0 time=135 us)
5 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=98 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=1 us)
19230 INDEX RANGE SCAN DUMMY_TABLE_DATE_IDX01 (cr=1364 pr=0 pw=0 time=19323 us)
上記のようにSQLを変更すると、既存の2,799ブロックから1,366ブロックに約2倍ほどの性能改善効果があることが分かりました。
以下は、任意の日付データをConnect By Levelで生成し、改善した事例を紹介するようにします。
[パフォーマンス問題SQL]
SELECT a.*, b.*
FROM tb_cd a,
tb_ch b
WHERE a.dt BETWEEN:b1 AND:b2 --->一日データ照会(:B1課:B2が同じ日時である)
AND a.seq BETWEEN :b3 AND :b4
AND a.dt = b.dt(+)
AND a.seq = b.seq(+)
ORDER BY a.dt, a.seq
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.03 0.04 0 0 0 0
Fetch 533 2.38 57.76 12596 22264 0 7980
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 535 2.41 57.81 12596 22264 0 7980
Rows Row Source Operation
------- ---------------------------------------------------
7980 FILTER (cr=22264 pr=12596 pw=0 time=52384285 us)
7980 NESTED LOOPS OUTER (cr=22264 pr=12596 pw=0 time=52376289 us)
7980 TABLE ACCESS BY INDEX ROWID TB_CD (cr=13745 pr=12591 … time=52035670 us)
7980 INDEX RANGE SCAN TB_CD_PK (cr=12928 pr=12397 pw=0 time=41443943 us)
6 TABLE ACCESS BY INDEX ROWID TB_CH (cr=8519 pr=5 pw=0 time=155190 us)
6 INDEX RANGE SCAN TB_CH_PK (cr=8513 pr=2 pw=0 time=85871 us)
Note. [性能問題SQL]はWhere節条件の日期間は常に1日で、TB_CDテーブルはトランザクション(UPDATE,DELETE)が頻繁です。
[WHERE節インデックス構成情報]
INDEX_NAME TYPE U COLUMN LIST
------------ ---- - ------------
TB_CD_PK NORM U DT, SEQ
前述の[パフォーマンス問題SQL]は、インデックスの先頭列がBETWEENと照会され、インデックス構成列のうち先頭列を除いた残りの列条件がIndex Filter Predicateで行われるため、非効率が発生した場合です。 トレース結果を見るとインデックスから抽出されたデータが7,980件であるが、インデックスを読み取ったブロック量は12,928ブロックで抽出結果より多いことが分かります。
このテーブルは、頻繁なトランザクション（UPDATE、DELETE）のためにすでに非効率的にインデックスブロックが増えており、SEQ列がIndex Filter Predicateで実行され、多くのブロックを読み取ることになっています。 INDEX REBUILD を実行して不要なインデックスブロックを整理してくれれば、非効率が一時的に減少するが、ある程度時間が流れれば、また再び性能問題が発生するでしょう。
したがって、BETWEENで照会された日付列を任意のデータを生成した後に結合に置き換え、SEQ列の条件をIndex Access Predicateで処理できるようにしてパフォーマンスを向上させる必要があります。 日付データを持つ列のデータ型がCHAR、VARCHAR2であり、列のデータが年月日データ（YYYYMMDD）の形であれば、BETWEEN条件に対して任意の日付データを生成してテーブル結合に置き換えて処理することができます。
次の[改善後SQL]は、Connect By Levelで任意のデータを生成した後、日付の範囲条件をなくし、任意のデータを生成したインラインビューと結合で処理するように変更することでパフォーマンスが大幅に改善されました。
[改善後SQL]
SELECT /*+ LEADING(D A) USE_NL(D A) USE_HASH(B) INDEX(A TB_CD_PK) */
a.*, b.*
FROM tb_cd a ,
tb_ch b,
( ---> Dummy Join追加
SELECT TO_CHAR(TO_DATE(:b32,'yyyymmdd')+LEVEL-1,'yyyymmdd') AS chk_date
FROM DUAL
CONNECT BY LEVEL <= (TO_DATE(:b33,'yyyymmdd')-TO_DATE(:b32,'yyyymmdd')+1)
) d
WHERE a.dt BETWEEN :b1 AND :b2
AND a.seq BETWEEN :b3 AND :b4
AND a.dt = b.dt(+)
AND a.seq = b.seq(+)
AND d.chk_date = a.dt --->新規追加
ORDER BY a.dt, a.seq ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.01 0 0 0 0
Fetch 533 0.10 0.17 0 343 0 7980
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 535 0.12 0.19 0 343 0 7980
Rows Row Source Operation
------- ---------------------------------------------------
7980 SORT ORDER BY (cr=343 pr=0 pw=0 time=137906 us)
7980 FILTER (cr=343 pr=0 pw=0 time=94876 us)
7980 HASH JOIN OUTER (cr=343 pr=0 pw=0 time=86893 us)
7980 TABLE ACCESS BY INDEX ROWID TB_CD (cr=338 pr=0 pw=0 time=64017 us)
7982 NESTED LOOPS (cr=26 pr=0 pw=0 time=31986 us)
1 VIEW (cr=0 pr=0 pw=0 time=137 us)
1 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=41 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us)
7980 INDEX RANGE SCAN TB_CD_PK (cr=26 pr=0 pw=0 time=8058 us)
6 TABLE ACCESS BY INDEX ROWID TB_CH (cr=5 pr=0 pw=0 time=118 us)
6 INDEX RANGE SCAN TB_CH_PK (cr=2 pr=0 pw=0 time=46 us)
これまでWhere句にある条件のうちインデックス末尾カラム条件が効率的であるにもかかわらず、先頭カラムが範囲条件で実行され、非効率が発生した場合に任意のデータを抽出した後、これを結合に置き換えてパフォーマンスを改善する方法について調べてみました。
追加のI / O（10gから）を必要としないDUALテーブルとConnect By Levelを使用して任意のデータを抽出してパフォーマンスを向上させる方法は、さまざまな状況で便利に使用できるため、必ず理解して積極的に活用して効率的なSQLを作成 するのに役立つことを願っています。
二回にわってお送りしました「任意のデータを活用した効率的なSQLの作成」は、いかがでしたでしょうか？
次回からは、「カラム変形によるSQL性能問題の理解する」と題しまして、二回シリーズでお送りします。ご期待ください。では、See You ^^
