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

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

新規登録して質問してみよう
ただいま回答率
85.50%
Oracle Database

Oracle Databaseは、米オラクルが開発・販売を行うリレーショナルデータベース管理システムです。

DELETE

ファイルシステムからファイル、データベースからレコードを削除することまたはメモリ内のオブジェクトの割り当てを取り消すことをさします。もしくは、HTTPプロトコルのDELETEを指すこともあります。

Oracle

Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

6434閲覧

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

退会済みユーザー

退会済みユーザー

総合スコア0

Oracle Database

Oracle Databaseは、米オラクルが開発・販売を行うリレーショナルデータベース管理システムです。

DELETE

ファイルシステムからファイル、データベースからレコードを削除することまたはメモリ内のオブジェクトの割り当てを取り消すことをさします。もしくは、HTTPプロトコルのDELETEを指すこともあります。

Oracle

Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2019/12/24 14:18

質問内容

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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

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

SQL

1DELETE FROM TEST_TABLE t 2WHERE (col1, col2, col3) in ( 3 SELECT col1, col2, col3 FROM TEST_VIEW 4 )

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

投稿2019/12/25 02:38

編集2019/12/25 16:28
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2019/12/25 12:10

sazi様 コメントありがとうございます。 ご提示いただいたSQLだとINDEXが使用されていました。 恐らく今後も同様の事象は発生する可能性がある為、改善案として調査依頼元に提示したいと思います。 有難うございます。 また、不勉強で申し訳ないのですが、以下の一文について質問させてください。 >突合する先にインデックスが無いなら、駆動表についてもインデックスを使用しない方が効率的だと判断する 単語としての意味は分かるのですが、処理のイメージがいまいち沸いてきませんでした…。 TEST_VIEWからFunctionを除外するとTEST_TABLEのINDEXが使用されることを実行計画で確認しています。 Functionを追加することで何故「突合する先にINDEXが無い」と判断されるのかが分からず、自分の中で納得のいく答えが出てきませんでした…。 ご面倒をお掛けし申し訳ないのですが、ご教示頂きたく宜しくお願い致します。
sazi

2019/12/26 01:06 編集

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

2019/12/25 16:25 編集

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

退会済みユーザー

2019/12/27 13:08

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

0

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

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

投稿2019/12/24 15:50

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2019/12/24 21:44

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

2019/12/25 00:26

なぜかは、オプティマイザーがそういう実行計画を採用した方がパフォーマンスが良いと判断したからです。 回答にはオプティマイザーが判断した内容を確認する方法を提示しました。 実行計画を確認する前に 統計情報の収集 https://www.shift-the-oracle.com/performance-tuning/dbms_statistics.html も必要です。
退会済みユーザー

退会済みユーザー

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

2019/12/25 13: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 13:17

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問