質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.09%

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

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 2,433

score 8

質問内容

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/INSERTDELETE 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が使用されるようになることは確認しています。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/12/25 21:10

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

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

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

    キャンセル

  • 2019/12/26 01:03 編集

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

    キャンセル

  • 2019/12/26 01:19 編集

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

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

    キャンセル

  • 2019/12/27 22:08

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

    キャンセル

+1

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/12/25 21:43

    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の有無で実行計画の結果が変わる」というロジック的な根本理由が知りたいのです。
    ご面倒をお掛けしますが、何卒宜しくお願い致します。

    キャンセル

  • 2019/12/25 22:04

    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) を調べたり、オラクル・サポートに質問されては?

    キャンセル

  • 2019/12/27 22:17

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.09%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る