oracleの実行計画について

Question

ORACLEのストアドプロシージャであるSQLが流すと、
意図していないindexが利用されて応答が非常に遅くなります。

USER_TABLES表で対象テーブルの統計情報の取得日時を確認しましたが、定期的に収集されていました。

対処療法ではありますが、SQL自体の組み換えや、インデックスの再構築を試すと早くなるのですが、またしばらくすると遅くなります。

ヒント句を与える手も検討していますが、パラメータの値次第で使うべきインデックスも違ってくるので簡単にはいきません。
簡単に書くと以下のような状態です。

SELECT ～
FROM 注文テーブル
WHERE 注文日 BETWEEN :注文日F AND :注文日T
　AND 納品日 BETWEEN :出荷日F AND :出荷日T
　AND 納品日 BETWEEN :出荷日F AND :納品日T

注文日、出荷日、納品日にはそれぞれインデックスが貼ってあり、
注文日が指定されれば注文日、出荷日が指定されれば出荷日のインデックスを
それぞれ使うようにしたいのです。

＜環境＞
Oracle11g

Comments

[sazi]

チューニングに関する相談なら、「簡単に書く」のではなく忠実に記述して下さい。

Answer 1

注文日、出荷日、納品日にはそれぞれインデックスが貼ってあり、

多いんですよね。何も考えずに闇雲に列毎に個別にインデックスを設定する人。

注文日、出荷日、納品日などそのWHERE句に載るそのテーブルの列のほとんどか全部を指定したひとつのインデックスを用意してください。きちんと統計情報も取得します。インデックスの列の順番も大切な場合がありますから、実行計画で確認してください。

質問に
Oracle11.1 or Oracle11.2 のどちらか？
CREATE TABLE, CREATE INDEX, テーブル中のデータを何件かのINSERTで文, 現行の統計情報を取得した方法, 現行の実行計画をMarkdown の [コード] で追記できると適切なコメントが付き易いです。

Comments

[tika5711]

回答ありがとうございます。
インデックス＝ツリーのようなイメージをしていたので、複合インデックスも同じように考えていました。
なので、WEHRE句に指定した最初の項目が全範囲の場合は、あまり効果がないと思っていました。どうやら違うようですね。
アドバイス頂いた内容で、WEHRE句に載っている項目で複合インデックスを貼ってみたところ、レスポンスは改善しました。

本番環境の為、実行中のSQLを捕まえる（＝遅いとき）しか実行計画を見る事ができなかったので、SELECT文を書いて試しましたが、コストが下がったので「効果あり」と判断しました（＾＾；