2021.12.22
SQLチューニング（第21回）「FUNCTION実行とSQL性能問題の理解」(2/3)
今回は、「FUNCTION実行とSQL性能問題の理解」に関する第二回目となります。
それでは、早速、初めていきましょう。
６.２ FUNCTION動作方式を理解すること
６.２.１ SELECT節に使うFUNCTIONの動作方式
基本的なFunctionの作成と使い方を説明し、Select句で使用されたNot Deterministic Functionの場合、Main SQLの抽出件数だけFunctionが繰り返し行われると述べました。 もしそうであるならば、さまざまな種類のトレース結果でFunctionがどのように実行されるのかを見てみることにします。
Script. Functionテスト用
DROP TABLE FUNCTION_TABLE PURGE;
DROP TABLE C1_CODE_NM PURGE;
DROP TABLE C2_CODE_NM PURGE;
< FUNCTION_TABLE >
■生成要件
-テーブル データ件数は100,000件
-カラムC1は値の種類が100,000種類すなわち、UNIQUE性格のカラム.
-カラムC2は値の種類が二つ
■テーブル生成
CREATE TABLE FUNCTION_TABLE AS
SELECT LEVEL C1,
MOD(LEVEL, 2) C2,
CHR(65+MOD(LEVEL,26)) C3,
MOD(LEVEL, 3) +1 C4
FROM DUAL
CONNECT BY LEVEL <= 100000;
■各カラムにインデックス生成および統計情報収集
CREATE UNIQUE INDEX IDX_FUNCTION_TABLE_C1 ON FUNCTION_TABLE(C1);
CREATE INDEX IDX_FUNCTION_TABLE ON FUNCTION_TABLE(C2,C3);
CREATE INDEX IDX_FUNCTION_TABLE_C4 ON FUNCTION_TABLE(C4);
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'FUNCTION_TABLE',CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
[カラムC1に対するコード性テーブル生成およびインデックス生成]
< C1_CODE_NM >
■ UNIQUEあるC1値に対するコード性テーブル生成
CREATE TABLE C1_CODE_NM AS
SELECT LEVEL C1,
LEVEL||'C2 CODE VALUE' C2,
CHR(65+MOD(LEVEL,20)) C3,
MOD(LEVEL,5) C4
FROM DUAL
CONNECT BY LEVEL <= 100000;
■インデックス生成および統計情報収集
CREATE UNIQUE INDEX IDX_C1_CODE_NM ON C1_CODE_NM(C1);
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'C1_CODE_NM',
CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
[カラムC2に対するコード性テーブル生成およびインデックス生成]
< C2_CODE_NM >
■三つ値に対するコード性テーブル生成
CREATE TABLE C2_CODE_NM AS
SELECT MOD(LEVEL, 3) C1,
CHR(65+MOD(LEVEL,3)) C2
FROM DUAL
CONNECT BY LEVEL <= 3;
■インデックス生成および統計情報収集
CREATE UNIQUE INDEX IDX_C2_CODE_NM ON C2_CODE_NM(C1);
EXEC dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'C2_CODE_NM',
CASCADE=>TRUE,ESTIMATE_PERCENT=>100) ;
[ FUNCTION生成]
■カラムC1値に対するコード名を持ってくるFUNCTION生成
CREATE OR REPLACE FUNCTION FN_C1_CODENM(P_CODE NUMBER)
RETURN VARCHAR2 IS
V_P_CODE_NM VARCHAR2(100);
BEGIN
SELECT C2 INTO V_P_CODE_NM
FROM C1_CODE_NM
WHERE C1 = P_CODE;
RETURN V_P_CODE_NM;
END;
■カラムC2値に対するコード名を持ってくるFUNCTION生成
CREATE OR REPLACE FUNCTION FN_C2_CODENM(P_CODE NUMBER)
RETURN VARCHAR2 IS
V_P_CODE_NM VARCHAR2(100);
BEGIN
SELECT C2 INTO V_P_CODE_NM
FROM C2_CODE_NM
WHERE C1 = P_CODE;
RETURN V_P_CODE_NM;
END;
CASE [1]. 10万件を持つテスト テーブルを条件なしで問い合わせた場合
SELECT c1,
fn_c1_codenm(c1) c2,
c3
FROM FUNCTION_TABLE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 6668 4.85 4.60 0 6858 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6670 4.85 4.65 0 6859 0 100000
Rows Row Source Operation
------- ---------------------------------------------------
100000 TABLE ACCESS FULL FUNCTION_TABLE (cr=6858 pr=0 pw=0 time=2838 us)
上記トレース結果を見ると、Main SQLの抽出データは合計10万件です。 CPU TimeやElapsed Timeは4秒以上かかり、読み込んだ総ブロックが6,858ブロックで表現されています。 ブロックI / Oスループットだけを見ると、効率的に行ったように見えるかもしれません。 しかし、CASE [1]のSQLトレース結果だけで効率的かどうかを判断することは不十分です。 なぜなら、Functionの実行結果が合算されなかったからです。 Functionを使用したSQLのトレースは、Main SQLとFunctionの実行結果が別にあるため、どちらも確認する必要があります。 したがって、Main SQLのトレース結果のみを報告してパフォーマンスを分析することは、不正確なパフォーマンス分析の結果を導く可能性があります。
それでは正確な判断のためにFunctionの実行結果まで確認してみます。
[FUNCTIONの実行内訳]
SELECT c2
FROM C1_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 5.77 5.84 0 0 0 0
Fetch 100000 0.65 3.47 211 300000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200001 6.42 9.31 211 300000 0 100000
Functionの実行結果を見ると、Executeは10万回でMain SQLの抽出データである合計10万件と同じ回数で繰り返し実行されたことを確認することができます。 また、Functionを10万回実行するのに要した時間がなんと9.3秒にもなり、Functionを実行しながら合計30万ブロックを処理したことが分かります。
Functionが1回実行ごとにかかる時間は0.000093秒、1回実行ごとに読み取るべきブロックは3ブロックに過ぎず、1回実行時の性能は非常に良好ですが、10万回という実行回数が結局該当SQLの性能を非効率にしてしまいました。
CASE [2]. WHERE節を追加して抽出データを減らした場合
SELECT c1,
FN_C1_CODENM(c1) c2,
c3
FROM FUNCTION_TABLE
WHERE c2 = 0
AND c3 = 'A'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.03 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 258 0.23 0.18 0 461 0 3846
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 260 0.23 0.21 0 463 0 3846
CASE [1]はWhere句なしで実行され、FUNCTION_TABLEのデータ数と同様にFunctionが10万回行われました。 しかし、CASE [2]はWhere句にC2 = 0 AND C3 = ‘A’条件を追加しました。 それでは、CASE [2]でのFunctionの実行回数がCASE [1]のように10万回実行されたのでしょうか。
[FUNCTIONの実行内訳]
SELECT c2
FROM C1_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3846 0.21 0.21 0 0 0 0
Fetch 3846 0.03 0.03 0 11538 0 3846
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7693 0.24 0.25 0 11538 0 3846
CASE [1]で抽出件数が10万件のときにFunctionが10万回行われたものと比較すると、CASE [2]の場合は、実行回数が3,846回に大きく減少しました。 Fucntionの実行回数とCASE [2]の総抽出件数は一致します。
それでは、Functionが行われる時点は、Where句を満たすデータをFetchするときに行うのでしょうか？
CASE [2]と同じ条件を持つSQLにROWNUM = 1条件を追加し、1つだけ抽出するようにしました。 データをFetchするときにFunctionが行われる場合、1件だけFetchするため、Functionも一度だけ行われるはずです。 本当にそうなのかCASE[3]を通して調べてみましょう。
CASE [3]. ROWNUM = 1条件を追加して1件が抽出されるように誘導した場合
SELECT c1,
FN_C1_CODENM(c1) c2,
c3
FROM FUNCTION_TABLE
WHERE c2 = 0
AND c3 = 'A' AND ROWNUM = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 0 7 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 COUNT STOPKEY (cr=3 pr=0 pw=0 time=45 us)
1 TABLE ACCESS BY INDEX ROWID FUNCTION_TABLE (cr=3 pr=0 pw=0 time=41 us)
1 INDEX RANGE SCAN IDX_FUNCTION_TABLE (cr=2 pr=0 pw=0 time=24 us)
トレース結果を見ればSQLの抽出データは1件です。 Functionの実行内容を確認してみましょう。
[FUNCTIONの実行内訳]
SELECT c2
FROM C1_CODE_NM
WHERE c1 = :b1
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 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1
抽出データも1件、Functionも1回行われました。 これは、Select句で使用されるFunctionの場合、データFetch時に行われることを意味します。
Functionの繰り返し実行によって発生するSQLのパフォーマンス問題解決のためには、ここで知ったことを明確に理解し、活用しなければなりません。しっかり覚えておいてください。
６．２．２ WHERE節に使うFUNCTIONの動作方式
これまでのテストでは、Select句で使用されているFunctionは抽出の数だけ繰り返され、FetchステップでFunctionが実行されることがわかりました。 それでは、Where句で使用されるFunctionがどのように動作するかを見てみましょう。
Where句に記述されたFunctionは、結合順序や結合方法、Where句によって抽出されたデータ件数など、さまざまな要素によって、Functionの動作方式が少し複雑な形をとります。
したがって、最大限の理解を助けるために、6つのCASEでWhere句で使用されているFunctionの動作方法について学んでいきます。
CASE [1]. T1を先に実行してHASH JOIN処理する場合
SELECT /*+ LEADING(T1) USE_HASH(T1 T2) */
t1.*,
t2.*
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND t1.c1 = t2.c1
AND t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 5 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 53 4.11 4.36 0 725 0 768
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 55 4.11 4.37 0 730 0 768
Rows Row Source Operation
------- ---------------------------------------------------
768 HASH JOIN (cr=160725 pr=0 pw=0 time=10384443 us)
3846 TABLE ACCESS FULL FUNCTION_TABLE (cr=204 pr=0 pw=0 time=3894 us)
15000 TABLE ACCESS FULL C1_CODE_NM (cr=160521 pr=0 pw=0 time=10352492 us)
トレース結果のTOTAL部分を見ると、最終抽出件数は768件であり、I/Oスループットは730ブロックで非常に効率的に行われたように見えます。 しかし、前述のようにSQL内にFunctionが存在する場合、Recursive Callの分析を必ずしなければなりません。
Function実行内容を見るのに先立ち一つ質問をします。
「Select句に使用されたFunctionのように抽出件数回（768回）実行されるのですか？」
質問に対する答えを考えてみてください。それでは、以下の結果を確認してみましょう。
[FUNCTIONの実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 100000 5.81 5.57 0 0 0 0
Fetch 100000 0.39 0.43 0 160000 0 60000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200001 6.20 6.02 0 160000 0 60000
ファンクションは合計10万回行われました。なぜ10万回行われたののでしょうか？その理由は、Functionを呼び出すT2テーブルのデータを減らす条件がなく、T1とT2の結合方式がHash Joinであるためです。単に結論だけでは、Functionの実行回数がなぜ10万回なのか十分に理解されないかもしれません。それでは、CASE [1]の説明を詳しく見てみましょう。
CASE [1]でFunctionが実行される回数と実行されるタイミングを正確に理解するためには、Hash Joinの実行方法を知る必要があります。 Hash Joinという用語自体が不慣れな場合は、Hash Joinに関する内容を一度確認し、理解してください。ここでは簡単に基本概念だけを説明するようにします。
Hash Joinは、最初に実行するテーブル（先行テーブル）に対して定数条件でデータをフィルタリングし、結合キー列でHash Mapを作成します。その後、Probe Table（後続テーブル）を実行しますが、このとき定数条件がある場合は、まず定数条件で対象を減らします。その後、先行テーブルが生成しておいたHash Mapにあるデータの中から結合に成功したデータを抽出します。
CASE [1]のテーブルT1は、T1.C2 = 0 AND T1.C3 = ‘A’条件によりデータを減らすことができますがが、T2は定数条件はなく、T2.C3 = FN_C2_CODENM（T2.C4）条件があります。ただし、FN_C2_CODENM（T2.C4）の結果値は、T1とT2がHash Joinを行う前に抽出する必要があります。したがって、T2テーブルの合計データ数だけFunctionが行われたのです。
つまり、Hash Joinで実行されたときにWhere句のFunctionは、結合を処理する前にデータをフィルタリングしたときに実行されたことがわかります。 10万件に対してすべてFunctionを行い、該当条件を満たすデータは15,000件で、この結果をもってHash Joinをすればデータは768件に減らすことになります。
CASE [2]. WHERE節にT2テーブルの条件を追加
SELECT /*+ LEADING(T1) USE_HASH(T1 T2) */
t1.*,
t2.*
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND t1.c1 = t2.c1
AND t2.c4 IN (2, 4)
AND t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 5 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 1.88 1.85 0 699 0 384
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 1.88 1.88 0 704 0 384
Rows Row Source Operation
------- ---------------------------------------------------
384 HASH JOIN (cr=60699 pr=0 pw=0 time=4278423 us)
3846 TABLE ACCESS FULL FUNCTION_TABLE (cr=204 pr=0 pw=0 time=82 us)
5000 TABLE ACCESS FULL C1_CODE_NM (cr=60495 pr=0 pw=0 time=4265307 us)
結合方法と結合順序はCASE [1]と同じですが、テーブルT2にT2.C4 IN（2、4）という条件を追加しました。 どのような違いが発生するのでしょうか？ CASE[1]と同じように10万回行われるのでしょうか？
[FUNCTIONの実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 40000 2.18 2.25 0 0 0 0
Fetch 40000 0.18 0.16 0 60000 0 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80001 2.37 2.42 0 60000 0 20000
「T2.C4 IN (2, 4)」条件を追加したところ、Functionの実行回数が10万回から4万回に減りました。 なぜこのような現象が発生したのでしょうか？ なぜなら、T2.C3 = FN_C2_CODENM（T2.C4）を実行する前にT2.C4 IN（2、4）条件で抽出された4万件に対してのみ、追加的にFunctionを行ったからなのです。
次に、CASE [3]はCASE [1]にT1とT2の結合方法をHash JoinからNested Loops Joinに変えたときのFunctionの実行回数にどのような変化があるかを調べてみます。
CASE [3]. 結合方法がNESTED LOOPS JOINである場合
SELECT /*+ LEADING(T1) USE_NL(T1 T2) */
t1.*,
t2.*
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND t1.c1 = t2.c1
AND t2.c3 = FN_C2_CODENM(t2.C4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.07 0 5 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 53 0.21 1.83 0 8002 0 768
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 55 0.23 1.90 0 8007 0 768
Rows Row Source Operation
------- ---------------------------------------------------
768 NESTED LOOPS (cr=14156 pr=0 pw=0 time=2151094 us)
3846 TABLE ACCESS FULL FUNCTION_TABLE (cr=256 pr=0 pw=0 time=249993 us)
768 TABLE ACCESS BY INDEX ROWID C1_CODE_NM (cr=13900 pr=0 pw=0 time=1895996 us)
3846 INDEX UNIQUE SCAN IDX_C1_CODE_NM (cr=3900 pr=0 pw=0 time=542664 us)
CASE[3] トレース結果を CASE[1] と比較してみると、結合方法が変わっただけで総抽出件数は 786 件に等しく、I/O スループットは CASE[1] に比べ 10 倍以上増加して約 8,000 ブロックを 読んでいます。 ブロック数が増加したため、CASE[1]より非効率的と判断することもできますが、先に強調したようにFunctionがある場合、Functionの実行内容まで必ず分析しなければ正確な判断ができません。
[FUNCTIONの実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3846 0.26 0.28 0 0 0 0
Fetch 3846 0.00 0.02 0 6154 0 2308
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7693 0.26 0.32 0 6154 0 2308
上記トレース結果を見ると、CASE [1]ではFunctionを10万回行ったが、結合方法が変わるとFunctionの実行回数が3,846回に大きく減少した。同じSQLであるにもかかわらず、CASE [1]とCASE [3]のFunctionの実行回数に大きな違いが発生したのがみて取れます。これは、Nested Loops JoinとHash Joinの処理方法による違いです。 Nested Loops Joinは、先行テーブルにアクセスした後、結合条件で後続テーブルを繰り返し探索して結合を実行します。したがって、T1を条件で抽出したデータ件数だけ結合を実行し、Functionが3,846回実行されるようになったのです。
もし、結合に失敗したり、別条件でデータがより分られたとすれば、該当データに対してはFunctionをあえて実行する必要がありません。 なぜなら、Functionは結合まで全部成功した件に対して、最も最後に実行しデータを抽出してもかまわないためです。 該当内容はCASE[4]のテストを通じて調べてみましょう。
CASE [4]. T2にT2.C4 IN (2,4)定数条件を付与した場合
SELECT /*+ LEADING(T1) USE_NL(T1 T2) */
t1.*,
t2.*
FROM FUNCTION_TABLE T1,
C1_CODE_NM T2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND t1.c1 = t2.C1
AND t2.c4 IN (2, 4)
AND t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 5 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 0.14 0.09 0 7950 0 384
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 0.14 0.11 0 7955 0 384
Rows Row Source Operation
------- ---------------------------------------------------
384 NESTED LOOPS (cr=10257 pr=0 pw=0 time=189078 us)
3846 TABLE ACCESS FULL FUNCTION_TABLE (cr=230 pr=0 pw=0 time=7748 us)
384 TABLE ACCESS BY INDEX ROWID C1_CODE_NM (cr=10027 pr=0 pw=0 time=180366 us)
3846 INDEX UNIQUE SCAN IDX_C1_CODE_NM (cr=3874 pr=0 pw=0 time=9164 us)
トレース結果を見ると、先行テーブルの件数はCASE[3]と同じであり、CASE[3]に比べて後続テーブルに条件が追加され、抽出データが半分に減少した以外は特に違いがないように見えます。
[FUNCTIONの実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1538 0.04 0.08 0 0 0 0
Fetch 1538 0.00 0.00 0 2307 0 769
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3077 0.04 0.09 0 2307 0 769
しかし、Functionの遂行の内訳を見ると、Functionの実行回数が1,538回で、CASE [3]に比べて半分近く減りました。 なぜ実行回数が減少したのでしょうか？
SELECT COUNT(*)
FROM FUNCTION_TABLE T1
WHERE t1.c2 = 0
AND t1.c3 = 'A' ;
COUNT(*)
---------
3846
上記SQLの抽出件数は3,846件です。T1の抽出データは3,846件と同じです。 CASE[3]に比べて先行テーブルに対して別の条件が追加されたり変更されたことがないので当然の結果です。 したがって、Nested Loops Joinの特性上、ジョインキーを用いて3,846回繰り返してジョインを試みなければならない点ではCASE [3]と全く違いがありません。
SELECT /*+ LEADING(T1) USE_NL(T1 T2) */
COUNT(*)
FROM FUNCTION_TABLE T1,
C1_CODE_NM T2
WHERE T1.C2 = 0
AND T1.C3 = 'A'
AND T1.C1 = T2.C1
AND T2.C4 IN (2, 4) ;
--AND t2.c3 = FN_C2_CODENM(t2.c4) --注釈処理する。
COUNT(*)
----------
1538
上記のSQLは、Functionが使用された条件のみをコメントアウトしましたが、抽出データが1,538件でFunctionの実行回数と正確に一致します。
これが意味するのは、CASE [3]で述べたように、Nested Loops Joinが成功するとすぐにFunctionが実行されないことを意味します。
結合条件で結合が成功しても後続テーブルに条件がある場合、条件を実行して満足するデータに対してのみ、Functionを実行してデータを抽出することが確認できます。
整理すれば、2つのテーブルが結合条件で結合が成功した3,846件のうち、T2.C4 IN（2、4）条件を満たす1,538件に対してのみFunctionが行われました。 Functionを実行しながらFunctionが使用された条件を処理したら、最終的に769件を抽出することになります。
CASE [5]はCASE [3]と同じSQLであり、結合方法はCASE [3]と同様にNested Loops Joinを行うように誘導しました。最初に実行するテーブルをT1からT2に変更して実行するためのヒントを与えたSQLです。 CASE [3]と同様に、Functionは3,846回実行したのでしょうか？トレース結果と機能の実行履歴を見てみましょう。
CASE[5]. T2から実行してNESTED LOOPS JOINを選択する場合
SELECT /*+ LEADING(T2) USE_NL(T2 T1) */
t1.*,
t2.*
FROM FUNCTION_TABLE T1,
C1_CODE_NM T2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND t1.c1 = t2.c1
AND t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 5 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 53 4.33 5.86 211 30575 0 768
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 55 4.33 5.91 211 30580 0 768
Rows Row Source Operation
------- ---------------------------------------------------
768 NESTED LOOPS (cr=190575 pr=211 pw=0 time=12052722 us)
15000 TABLE ACCESS FULL C1_CODE_NM (cr=160521 pr=0 pw=0 time=10710013 us)
768 TABLE ACCESS BY INDEX ROWID FUNCTION_TABLE (cr=30054 pr=211 time=1332356 )
15000 INDEX UNIQUE SCAN IDX_FUNCTION_TABLE_C1 (cr=15054 pr=211 time=1291172 us)
[FUNCTIONの実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 6.05 5.74 0 0 0 0
Fetch 100000 0.42 0.45 0 160000 0 60000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200001 6.47 6.19 0 160000 0 60000
Functionのトレース結果を見ると、Functionの実行回数が10万回です。 理由は何でしょうか？
テーブルＴ２を最初に実行した場合、データを減らすことができる条件は存在しません。 したがって、T2テーブル全体のデータに対して「AND t2.c3 = FN_C2_CODENM(t2.c4)」条件を処理する必要があるため、Functionが10万回行われました。 そして「AND t2.c3 = FN_C2_CODENM(t2.c4)」によって抽出された15,000件はT1テーブルとNested Loops Joinを実行します。 前述のテストでは、SQLでFunctionを処理するときに結合順序、結合方法がどれほど重要かを知ることができます。
CASE [6]は、CASE [5]のSQLに「AND T2.C4 IN（2、4）」条件のみを追加しました。
CASE[6]. T2から読んで結合方法はNESTED LOOPS JOINを選択して定数条件が追加される場合
SELECT /*+ LEADING(T2) USE_NL(T2 T1) */
t1.*,
t2.*
FROM FUNCTION_TABLE t1,
C1_CODE_NM t2
WHERE t1.c2 = 0
AND t1.c3 = 'A'
AND t1.c1 = t2.C1
AND t2.c4 IN (2, 4)
AND t2.c3 = FN_C2_CODENM(t2.c4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 5 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 27 1.80 1.87 0 10523 0 384
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 29 1.82 1.88 0 10528 0 384
Rows Row Source Operation
------- ---------------------------------------------------
384 NESTED LOOPS (cr=70523 pr=0 pw=0 time=4301208 us)
5000 TABLE ACCESS FULL C1_CODE_NM (cr=60495 pr=0 pw=0 time=4265206 us)
384 TABLE ACCESS BY INDEX ROWID FUNCTION_TABLE (cr=10028 pr=0 …)
5000 INDEX UNIQUE SCAN IDX_FUNCTION_TABLE_C1 (cr=5028 pr=0 pw=0 time=20161 us)
CASE [5]に比べてT2に条件が追加され、T2の抽出データが15,000件から5,000件に減りました。 したがって、Nested Loops Joinでの繰り返し検索回数が減り、Function実行を除いたI/Oスループットも3万ブロックから1万ブロックに改善されました。 それでは、Functionの実行回数も減少したのか、実行履歴を見てみましょう。
[FUNCTIONの実行内訳]
SELECT c2
FROM C2_CODE_NM
WHERE c1 = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 40000 2.27 2.25 0 0 0 0
Fetch 40000 0.17 0.16 0 60000 0 20000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 80001 2.44 2.42 0 60000 0 20000
CASE[5]で10万回行ったのとは異なり、CASE[6]ではFunctionが4万回だけ実行されました。 CASE [5]で述べたように、条件がある場合は、Functionが存在する条件を処理する前にデータを減らします。 そのため、「SELECT count(*) from C1_CODE_NM t2 where T2.C4 IN (2, 4)」の結果値が 4万で Function の実行回数と一致します。 つまり、T2を最初に実行するときにT2.C4 IN（2、4）条件を処理してデータをフィルタリングした後、4万件に対してのみT2.C3 = FN_C2_CODENM（T2.C4）条件を処理して5,000件が最終抽出されました。
次のSQLを実行すると、正確に5,000件であることがわかります。
SELECT COUNT(*)
FROM C1_CODE_NM t2
WHERE T2.C4 IN (2, 4)
AND T2.C3 = FN_C2_CODENM(T2.C4) ;
COUNT(*)
----------
5000
これまで、結合方法、結合順序、条件によって Function の実行回数にも差が生じることが分かりました。 下の表は6つのCASEの結果をまとめたもので、結合順序と結合方法、そしてデータ件数によってFunctionの実行回数が影響を受けることが明確にわかります。 さらに、テストSQLは、結合順序がT1→T2順序で結合されたときにNested Loops Joinスキームを選択したときに最もパフォーマンスが高いことがわかります。 将来的には、SQLのWhere句にFunctionがある場合は、結合方法と結合順序を考慮してFunctionの実行回数を最適化してSQLのパフォーマンスのトラブルシューティングに役立つことを願っています。
テーブル6-1. Function実行回数テスト結果要約
| CASE | 結合順序 | 結合方法 | データ件数（T1) | データ件数（T2) | データ件数 （結合後 抽出件数) | Function 実行回数 |
| 1 | T1 →T2 | HASH JOIN | 3,846 | 100,000 | 768 | 100,000 |
| 2 | T1 →T2 | HASH JOIN | 3,846 | 40,000 | 384 | 40,000 |
| 3 | T1 →T2 | NESTED LOOPS JOIN | 3,846 | 100,000 | 768 | 3,846 |
| 4 | T1 →T2 | NESTED LOOPS JOIN | 3,846 | 40,000 | 384 | 1,538 |
| 5 | T2 →T1 | NESTED LOOPS JOIN | 3,846 | 100,000 | 768 | 100,000 |
| 6 | T2→T1 | NESTED LOOPS JOIN | 3,846 | 40,000 | 384 | 40,000 |
今回は、ここまでです。次回は、今回は、「FUNCTION実行とSQL性能問題の理解」に関する第三回目（最終回）となります。ご期待くさい。では、see you ^^
