同じ結合条件のDELETE文とMERGE文の実行でINDEX使用の挙動が異なる理由が知りたい

Question

質問内容

Oracle SQLの質問です。初心者の為、ご教示頂きたく宜しくお願い致します。
OracleのViewからTABLEへデータを投入するとき、TABLEとViewのキー項目を結合し、「DELETE/INSERT」または「MERGE」を発行し投入しています。
Functionが使われているViewをsourceにした場合の「DELETE/INSERT」と「MERGE」で処理速度が大幅に異なりました。
その後、該当SQLについて実行計画を取得した所「DELETE/INSERT」と「MERGE」でINDEX使用の挙動が異なることがわかりました。(後述参照)

結合している部分(WHERE句かON句か)が関係しているのか等々調べましたが、それらしい記述を見つけられませんでした。
SQL文の組み方の問題なのか、それともWHERE結合・ON結合の挙動の問題なのか判断が付かず、なぜDELETE文の方だけINDEXが使われていないのかという説明が出来ず困っています。

『全く同じ結合条件』を使用した2つのSQL文について、
何故INDEXの使用の挙動が異なるのかご教示頂けないでしょうか。

実行計画取得時のFilterの表示

①DELETE/INSERTを行っているSQL文について実行計画を取得すると
INDEXが使用されていないという結果が出ました。
(TABLE ACCESS FULL)
また、実際に発行されているSQLと異なった表示になっています。

　FUNCTION(SOURCE.col1):=B1 AND FUNCTION(SOURCE.col2):=B2 AND FUNCTION(SOURCE.col3):=B3

②同じキーを指定したMERGE文の場合は、INDEXが使用されているという結果が出ました。
(INDEX RANGE SCAN)
こちらの挙動は想定通りの挙動です。

TEST_TABLE.col1:=FUNCTION(SOURCE.col1) AND TEST_TABLE.col2:=FUNCTION(SOURCE.col2) AND TEST_TABLE.col3:=FUNCTION(SOURCE.col3)

実行計画取得時のSQL文(項目名は仮名に変更しています)

【SQL①　DELETE/INSERT】
DELETE FROM TEST_TABLE t WHERE EXISTS 
(SELECT 1 FROM TEST_VIEW s 
  WHERE t.col1=s.col1 AND t.col2=s.col2 AND t.col3=s.col3)

【SQL②　MERGE】
MERGE INTO TEST_TABLE t USING TEST_VIEW s ON
(t.col1=s.col1 AND t.col2=s.col2 AND t.col3=s.col3)
WHEN MATCHED THEN UPDATE SET 
t.col1=s.col1 AND t.col2=s.col2 AND t.col3=s.col3 t.col4=s.col4 AND t.col5=s.col5
WHEN NOT MATCHED THEN INSERT VALUES
(s.col1,s.col2,s.col3,s.col4,s.col5)

試したこと

DELETE文発行時、ViewからFunctionを外すとINDEXが使用されるようになることは確認しています。

Answer 1

関数を使用しているビューを使用している事と、相関副問い合わせという事が相まってインデックススキャンされないのではないかと思います。

以下の様な副問合せであれば改善されないでしょうか。

DELETE FROM TEST_TABLE t 
WHERE (col1, col2, col3) in ( 
        SELECT col1, col2, col3 FROM TEST_VIEW
      )

理由という事なら、deleteでは相関副問い合わせを使用しているからでしょうね。
相関副問合せは駆動表を処理しながら問合せを行うのが基本です。
ですので、突合する先にインデックスが無いなら、駆動表についてもインデックスを使用しない方が効率的だと判断するからです。

Comments

[退会済みユーザー]

sazi様
コメントありがとうございます。
ご提示いただいたSQLだとINDEXが使用されていました。
恐らく今後も同様の事象は発生する可能性がある為、改善案として調査依頼元に提示したいと思います。
有難うございます。

また、不勉強で申し訳ないのですが、以下の一文について質問させてください。
＞突合する先にインデックスが無いなら、駆動表についてもインデックスを使用しない方が効率的だと判断する

単語としての意味は分かるのですが、処理のイメージがいまいち沸いてきませんでした…。
TEST_VIEWからFunctionを除外するとTEST_TABLEのINDEXが使用されることを実行計画で確認しています。
Functionを追加することで何故「突合する先にINDEXが無い」と判断されるのかが分からず、自分の中で納得のいく答えが出てきませんでした…。
ご面倒をお掛けし申し訳ないのですが、ご教示頂きたく宜しくお願い致します。

[sazi]

ファンクションのパラメータがインデックスに含まれているので、インデックスとして十分と誤解されていませんか？
function(columnA)=X があったとして、この場合インデックスとして必要なのはファンクションの結果の値です。
その結果をインデックスとして使用するには、ファンクションインデックスを作成しておく必要があります。
columnAがインデックスに含まれていてそれを利用するなら、columnA=function(X)のような条件になっていなければなりません。

[sazi]

但し、ファンクションインデックスを作成しても、この相関副問合せのケースの場合インデックスが使われるかどうかはオプチマイザ次第です。
mergeの場合、ビューの結果セットとの突合ですからインデックスは使用されますが、相関副問合せは結果セットを使用する訳では無く都度問合せを行うから、全表検索になる可能性が高いと思います。
相関副問合せは駆動表の件数が少ない場合でしか、基本コスト面の効果はありません。

私の回答はビューの結果セットを使用するようにする事で、mergeと同様にインデックスが使われるだろうとの推測によるものです。

[退会済みユーザー]

sazi様
返信が遅くなり申し訳ございませんでした。
そもそも自分がどういう勘違いをしているか、という点について解消出来ました。
お時間頂きありがとうございました。

Answer 2

WHERE に関数1(COL1) = ...
と記述すると COL1 に適切なインデックスが設定されていてもインデックスは使われないで、TABLE ACCESS FULL(テーブルを全件走査)されるのでレコード件数が多いと極端にパフォーマンスが落ちます。
テーブル設計を含めて、関数1を使わないで条件を記述できるような方法を考えます。

質問に CREATE TABLE, CREATE INDEX, CREATE VIEW, CREATE FUNCTION と共にDELETE, MERGE を Oracle EXPLAIN PLAN を使って実行計画を取得する の結果を追記されては？

Comments

[退会済みユーザー]

Orlofsky様
コメントありがとうございます。

解りづらい表示で記載していたようですみません。
WHERE句自体には関数を入れていません。
結合条件では関数を設定していない方のTABLEを左に置いているのに、実行計画では「関数(SOURCE.COL1):=…」と言う表示になるのは何故なのか、と言うのが知りたい内容です。
MERGEと同じ様な結果になると思っていたので、何故だろう…と言うのが疑問点です。

[Orlofsky]

なぜかは、オプティマイザーがそういう実行計画を採用した方がパフォーマンスが良いと判断したからです。
回答にはオプティマイザーが判断した内容を確認する方法を提示しました。

実行計画を確認する前に 統計情報の収集 https://www.shift-the-oracle.com/performance-tuning/dbms_statistics.html も必要です。

[退会済みユーザー]

Orlofsky様
統計情報の更新は毎日自動で行われていることを確認しました。
そして度々言葉が足りず申し訳ございません。
私としては「なぜオプティマイザーがその実行計画を採用したのか」が知りたかった次第です。
前述のコメントに記載した「MERGEと同じ様な結果」と言うのはMERGE文でも同じ結合条件を使用していることから「DELETE文(Function有の結合条件)でもINDEXが使われる実行計画が出るはず」と思い込んでいた為です。
しかし実際はそうではありませんでした。

現状、関数を除外することでINDEXが使用されるようになり、処理速度が改善されました。
実行計画を取得したときは以下の様な表示となり、ほぼ想定通りの挙動であることを確認しました。
```
TEST_TABLE.col1:=SOURCE.col1 AND TEST_TABLE.col2:=SOURCE.col2 AND TEST_TABLE.col3:=SOURCE.col3
```

ただ、改善といっても「こうすれば早くなった」程度のもので、「そもそも何故このDELETE文について、投入元であるVIEWのキー項目にFunctionを使うとINDEXが使われなくなるのか？」と言う点についてが解消しておらず、今回の様な質問をさせて頂きました。
Oracleの仕組み上、SQL文の記載と異なり実際にはINDEXが使われなくなる条件(こういう書き方だと使われなくなる等)をご存知でしたら、ご教示頂けないでしょうか。
今回の様に「Functionの有無で実行計画の結果が変わる」というロジック的な根本理由が知りたいのです。
ご面倒をお掛けしますが、何卒宜しくお願い致します。

[Orlofsky]

CREATE TABLE 等を公開しないで条件の一部を定時されても
TEST_TABLE.col1 := SOURCE.col1 AND
TEST_TABLE.col2 := SOURCE.col2 AND
TEST_TABLE.col3 := SOURCE.col3
って書き方はエラーになりますよね？くらいしか書けません。

オプティマイザの情報って公開されている情報は少ないので、ググって
https://www.oracle.com/technetwork/jp/database/articles/tsushima/tsm05-1598251-ja.html
とかを調べてね、とか。

サポート契約が有効なら、My Oracle Support Knowledge Base(旧KROWN) を調べたり、オラクル・サポートに質問されては？

[退会済みユーザー]

Orlofsky様
返信が遅れて申し訳ありません。
曖昧な質問内容となってしまい申し訳ございませんでした。
今後はサポート側に問い合わせる様に致します。