前方一致で索引（インデックス）が使用されない？

質問

社員表に対して、SQLを実行します。
（社員名）は任意の文字列です。

select syainNO
from syain
where deleteflg = '0'
and syain_kubun = '2'
and syain_mei like '(社員名)%';

索引は以下の２つです。

// deleteフラグに対する索引・・・(a)
create index syain_index_1 on syain (
deleteflg
)

// 社員区分と社員名に対する索引・・・(b)
create index syain_index_2 on syain (
syain_kubun,
syain_mei
)

deleteflgは0と1しか存在せず、0がほとんどです。
syain_kubunは1と2のみで、1が9割、2が1割程度です。

このとき、SQL実行時にトレースを分析すると、
(a)が使用され、(b)は使用されていません。

deleteflgとsyain_kubunのデータ分布からすると、deleteflg = '0'の条件により、
明らかに(b)の方が絞り込みを行えると思うのですが、使用されないのはなぜでしょうか？

where syain_kubun = '2'
and syain_mei like '(社員名%) '
and deleteflg = '0';
のように、条件の順番を入れ替えても効果はありませんでした。

試しにwhere句を変えてみると、(b)の索引が使用されます。
（likeをやめ、=を指定）

where deleteflg = '0'
and syain_kubun = '2'
and syain_mei = '(社員名) ';

この２つの違いは何でしょうか？

解決策として、以下を考えています。
(a)の索引は事情があって削除はできません。

(i)deleteflg = '0'　を　deleteflg != '1'　にする
　→(a)の索引が有効にならないようにする。
　　ただし、パフォーマンスに影響がないか
(ii)deleteflg = '0'　を条件から外す
　→SQLを組み立てるjava側で、deleteflgの値によって
　　データ取得可否を判定するロジックが増えるのでちょっと。

これらの解決策に対するご意見、また、他に案がありましたら
よろしくお願いします。

回答No.2

whiteline507

３つほど。

１つは
b索引のカラム順序が悪いような気がします。
bの索引を使うとなるとまず1割程度までしか
絞込みを行えない社員区分での絞込みを行う必要があり、その分あまり効率のよい索引とはOracleはみなしません（その後の社員名で非常に少ない数まで絞り込めると分かっていても１つ目の絞込みが効率が悪ければOracleは候補からはずすことも十分に考えます。）
ですのでb索引のカラム順序を逆にして社員名から
絞込みを行わすようにすればOracleが選んでくれる確立が高くなります。
社員区分は１割程度の絞込みしか行わないということで
あまりOracleがこの索引を能動的に使うとは思えません。Oracleにといって１割もヒットするということは索引ではなく、全表走査の方が速いと決断することが多いです。

つぎにanalyzeをしっかりしてやるということです。
analyzeによってその表の中に何件のレコードがあって各カラムのデータ分布がどうなっているのかをしっかり把握できるのでより最適な実行計画をOracleが立てることができます。

最後に社員区分はNUMBER型ではないですよね？
SQL文を見ると''で囲っているのでおそらくCHARか
VARCHAR型だとおもうのですがここがずれていると
またインデックスを使う可能性がなくなってきます。

回答No.1

k_o_r_o_c_h_a_n

バージョンもオプティマイザも不明なので、回答しにくいのですが・・

どの索引を使うか、はたまた索引を一切使わないか、などの判断は、
最終的にオプティマイザの決めることなので、オプティマイザが
お望みの索引検索を行うと判断しやすいように方向付けすることしか
出来ません。

今回の場合、RBOでもCBOでも、オプティマイザヒントで索引を指定した方が
良い結果になるような気がします。（前置きのように確実な解決ではないけど）

select /*+ INDEX(syain syain_index_2) */ syainNO
from syain
where deleteflg = '0'
and syain_kubun = '2'
and syain_mei like '(社員名)%';

としてみた場合、どうなりますか？