あいまい検索のパフォーマンス向上について

あいまい検索(部分一致)検索をする際に、あいまい検索だとインデックスも効かないので、インデックスを張ってパフォーマンス向上という訳にもいかず、パフォーマンスが非常に悪くて困っています。
状況としては、WEBアプリケーションで、検索画面から検索条件を入力し、「Find」ボタンを押下するとJavaでSQLを発行し、Oracleへ接続するという仕組みのシステムです。

下記のようなケースで、後者のSQLに変更するとガツンとパフォーマンスが向上したのですが、偶然でしょうか?

【もともとのSQL】
select name
,age
,sex
,blood_type
,address
from TABLE1
where age >= 20
and age < 30
and blood_type = 'A'
and name like '%山%'
and address like '%中央区%'
/

【パフォーマンスが向上したSQL】
select * from
(select name
,age
,sex
,blood_type
,address
from TABLE1
where age >= 20
and age < 30
and blood_type = 'A'
)
where name like '%山%'
and address like '%中央区%'
/

というふうに、あいまい検索部分を別出ししました。

WHERE句の条件をANDでたくさんつなげる際に、2度の問い合わせにはなりますが、一度あいまい検索以外の条件で絞り込んで、それから再度その結果に対してあいまい検索を実施した方が早いのではないかと思ってやったところ実際に早くなったのですが、これは偶然でしょうか?
件数が増えると逆に遅くなるとかだと、逆効果なので、理論的にはどうなのか、ご存知の方がいらっしゃったら教えてください。

また、実際にはテーブルではなく、VIEWに対しての検索で実施しました。

よろしくお願い致します。

No.5 回答者： uresiiwa 回答日時： 2007/12/15 14:37

１．偶然かどうか？

オプティマイザの動作は実に複雑です。投稿された情報だけでは確実な答えがいえない面があるのですが、両方のSQLの実行計画を投稿していただければ、理論補完可能です。


２．Viewに対する問い合わせでインデックスが使われないケース
＞あいまい検索や、フィールドに関数や演算をかましているケースとか以外に、テーブルを直接みれば、インデックスが有効なのに、Viewなら無効になるケースって、どんなときなのでしょうか？

ひとつは、実行計画上、Viewがインスタンス化される場合です。インスタンス化とは、Viewのクエリ結果をいったんデータとしてすべて取得し、仮想的にひとつのテーブルのように扱うことです。こうなると、非常に大量のメモリを消費したり、もとの個別テーブルに対するインデックスが効かないため、Viewの中にViewを使うことや、Viewに対してさらに複雑なJOINを行うことは推奨されていません。
（なお、View内でUNIONやGROUP BYを行っていたり、複雑な内容であるとインスタンス化がおきやすいです。）

仮に、「もともとのSQL」でインデックスが使われておらず、「パフォーマンス向上したSQL」でインデックスが使われているとした場合、「もともとのSQL」のほうにインデックスヒントをつけることでもともとのSQLの書き方で同様のパフォーマンスを得られるはずです。
VIEW内のテーブルに対するインデックスヒントなので、「グローバルヒント」という書き方をしないといけませんのでご注意ください。


３．アドバイス
もしかするとヒントなども不慣れかもしれませんが、上記で出てくるOracle用語は、すべて説明がOracleパフォーマンス・チューニングガイドに載っています。（OTNなどからもダウンロードできるOraceマニュアル）
まずはこのガイドをひととおり読まれるのが、何よりおすすめのチューニング方法となります。

参考URL：http://otn.oracle.co.jp/

No.4 回答者： mas_sama 回答日時： 2007/12/15 07:38

まず、ビューなら無効になるケースは

他にどのような原因がありますか？
というご質問に対して回答させていただきます。

経験したことで満足に検証していないため
原因であるとは断定できませんが・・・。
ビューの元となるテーブルが別ＤＢサーバのテーブルで
ＤＢリンクによりリモートから参照していた場合にも
同じことを経験したことがあります。

以下、蛇足です。

システム開発現場では、プログラマの方が作成した
ＳＱＬを適切に評価するチームや担当者がいることもありますが
開発規模が小規模であったり、DBMSの有識者が不足している場合は
プログラマ自信がＳＱＬを評価せざるを得ないかもしれません。

No.1でも申し上げましたが、適切な環境で実行計画を試すと
性能に問題があるＳＱＬを探すことも可能と思います。

No.3の方の回答の通りオプティマイザの動作が
適切でないと言えばプログラマとしては偶然と言えると思います。
オプティマイザはＳＱＬを効率よく実行する仕組みです。
オプティマイザもプログラムですので
実際に効率が良く実行するかどうかは別で
本事象のようなことも発生します。
オプティマイザの動作を考えてＳＱＬを作成することは
難しいことかもしれませんが、本事象のように悪くなった
ケースを覚えたり、調べたことや人に聞いたことを
活用できれば、次の開発では未然に防げるかもしれませんね。

また、No.3の方のオプティマイザヒントの補足をさせていただくと
ＳＱＬのヒント句に記述するすることで、オプティマイザの動作を
ある程度制御することが可能です。
補足URL
http://www.asahi-net.or.jp/~QK4Y-ASZW/comp/oracl …

No.3 回答者： k_o_r_o_c_h_a_n 回答日時： 2007/12/15 03:42

>下記のようなケースで、後者のSQLに変更するとガツンとパフォーマンスが向上したのですが、偶然でしょうか?

偶然です。ですが、良くあることです。
本来、オプティマイザは、ビュー（インラインビュー含む）の検索に対し、検索条件の折り込みを行い、
テーブルを検索しようとします。
判断が適切でない場合に、条件の折り込みが出来ず、実行計画が変化します。
本来は、【もともとのSQL】でも【パフォーマンスが向上したSQL】でも同じでなければいけないのです。
例えば、当方の環境では
select * from emp where empno<7500 and deptno=20
select * from (select * from emp where deptno=20) where empno<7500
の２つは同じ実行計画です。（共にEMPNOによる索引検索）

よって、質問の回答としては、良くある偶然というのが適切です。

【もともとのSQL】は、オプティマイザの実行計画が適切でないだけの話なので、
オプティマイザヒントを書けば、【パフォーマンスが向上したSQL】と同様の結果は得られます。
たぶん、ageやblood_typeに索引が付いていると思うので、その索引を使用することを強要すれば同じになるでしょう。

No.2 回答者： mas_sama 回答日時： 2007/12/13 22:11

No1の回答の者です。

改めて質問の先頭を見たらOracleと記述が・・・ｗ
ならば、是非実行計画でSQLを比較してみてください。
時間さえかけてよければ、プログラマーとしては
楽しい作業（のハズ）ですよ。

No.1 回答者： mas_sama 回答日時： 2007/12/13 22:09

はじめましてt29x0479さん。

DBMSがOracleの場合と考えアドバイスさせていただきます。
ご質問の結論ですが「偶然」ではありません。

経験則のため一概にはいえませんが、Viewを検索する際に
元となるテーブルのインデックスが使用できるケースと
使用できないケースがあります。

また、Oracleであれば実行計画でSQLのコストを測ることで
ある程度定量的に評価することが可能です。
→SQLのコストをSQLを実行をして結果が返ってくる速度と
　思っていただいても結構です。
→Oracleの実行計画のとり方の参考URL
　http://biz.rivus.jp/sqlplus/tutorial/autotrace.h …
　DBMSの種類、バージョンが違っていたら
　見合った方法を探してみてください。

ご質問のSQLの動作を、拙い知識ではありますが
予測してみましたのでご参考願います。

【前提】
・View「TABLE1」は名前が紛らわしいため「View1」という名前で再定義します。
・「View1」は「Table1」と「Table2」の直積と仮定します。
・「Table1」はカラム「blood_type」があると仮定します。
・「Table1」を条件「blood_type = 'A'」で絞り込んだ結果を「Table1'」と仮定します。
・「Table1'」と「Table2」の直積を「View1'」と仮定します。

【もともとのSQL】
「Table1」と「Table2」を直積で得られた「View1」から
次の３つの条件
　・blood_type = 'A'
　・name like '%山%
　・address like '%中央区%'
でレコードを探していると考えられます。
つまり、「Table1」のレコード数×「Table2」のレコード数となる
「View1」から、全レコードを対象に検索していると予測されます。

【パフォーマンスが向上したSQL】
まず、「Table1」を次の条件で絞り込みます。
　・blood_type = 'A'
次に、絞り込んだ結果の「Table1'」と「Table2」を
直積した結果「View1'」から条件
　・name like '%山%
　・address like '%中央区%'
で絞込みます。
つまり、「Table1'」のレコード数×「Table2」のレコード数となる
「View1'」から、全レコードを対象に検索していると予測されます。

以上より
　【もともとのSQL】
　　「Table1」のレコード数×「Table2」のレコード数
と
　【パフォーマンスが向上したSQL】
　　「Table1'」のレコード数×「Table2」のレコード数
を比較すると
　【パフォーマンスが向上したSQL】
のほうが参照するレコード数が少ないため
実行結果が早くなっていると予測できます。

蛇足ですが、「blood_type = 'A'」は血液型でしょうから
日本人が満遍なく登録されているのであれば
全体の４割程度のレコード数でしょうか・・・。

対処方法に関しては是非考えていただきたいのですが
案を１つだけ挙げさせていただけると
Viewから参照するのではなく
各テーブル毎に参照してはいかがでしょうか？

最後に余談ですが、プログラムの動作、発生した事象で
偶然と思えたり、考えられたりする場合
事象発生の要因、原因を知らない
つまり必然であることが分からないという
プログラマーのカンチガイが多いです。

この回答へのお礼

わかりやすい解説、ありがとうございます。
実行計画の取り方を見て勉強します。

また、下記が気になりました。
---
経験則のため一概にはいえませんが、Viewを検索する際に
元となるテーブルのインデックスが使用できるケースと
使用できないケースがあります。

あいまい検索や、フィールドに関数や演算をかましているケースとか以外に、テーブルを直接みれば、インデックスが有効なのに、Viewなら無効になるケースって、どんなときなのでしょうか？

お礼日時：2007/12/15 00:44