
あいまい検索(部分一致)検索をする際に、あいまい検索だとインデックスも効かないので、インデックスを張ってパフォーマンス向上という訳にもいかず、パフォーマンスが非常に悪くて困っています。
状況としては、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に対しての検索で実施しました。
よろしくお願い致します。
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
1.偶然かどうか?
オプティマイザの動作は実に複雑です。投稿された情報だけでは確実な答えがいえない面があるのですが、両方のSQLの実行計画を投稿していただければ、理論補完可能です。
2.Viewに対する問い合わせでインデックスが使われないケース
>あいまい検索や、フィールドに関数や演算をかましているケースとか以外に、テーブルを直接みれば、インデックスが有効なのに、Viewなら無効になるケースって、どんなときなのでしょうか?
ひとつは、実行計画上、Viewがインスタンス化される場合です。インスタンス化とは、Viewのクエリ結果をいったんデータとしてすべて取得し、仮想的にひとつのテーブルのように扱うことです。こうなると、非常に大量のメモリを消費したり、もとの個別テーブルに対するインデックスが効かないため、Viewの中にViewを使うことや、Viewに対してさらに複雑なJOINを行うことは推奨されていません。
(なお、View内でUNIONやGROUP BYを行っていたり、複雑な内容であるとインスタンス化がおきやすいです。)
仮に、「もともとのSQL」でインデックスが使われておらず、「パフォーマンス向上したSQL」でインデックスが使われているとした場合、「もともとのSQL」のほうにインデックスヒントをつけることでもともとのSQLの書き方で同様のパフォーマンスを得られるはずです。
VIEW内のテーブルに対するインデックスヒントなので、「グローバルヒント」という書き方をしないといけませんのでご注意ください。
3.アドバイス
もしかするとヒントなども不慣れかもしれませんが、上記で出てくるOracle用語は、すべて説明がOracleパフォーマンス・チューニングガイドに載っています。(OTNなどからもダウンロードできるOraceマニュアル)
まずはこのガイドをひととおり読まれるのが、何よりおすすめのチューニング方法となります。
参考URL:http://otn.oracle.co.jp/
No.4
- 回答日時:
まず、ビューなら無効になるケースは
他にどのような原因がありますか?
というご質問に対して回答させていただきます。
経験したことで満足に検証していないため
原因であるとは断定できませんが・・・。
ビューの元となるテーブルが別DBサーバのテーブルで
DBリンクによりリモートから参照していた場合にも
同じことを経験したことがあります。
以下、蛇足です。
システム開発現場では、プログラマの方が作成した
SQLを適切に評価するチームや担当者がいることもありますが
開発規模が小規模であったり、DBMSの有識者が不足している場合は
プログラマ自信がSQLを評価せざるを得ないかもしれません。
No.1でも申し上げましたが、適切な環境で実行計画を試すと
性能に問題があるSQLを探すことも可能と思います。
No.3の方の回答の通りオプティマイザの動作が
適切でないと言えばプログラマとしては偶然と言えると思います。
オプティマイザはSQLを効率よく実行する仕組みです。
オプティマイザもプログラムですので
実際に効率が良く実行するかどうかは別で
本事象のようなことも発生します。
オプティマイザの動作を考えてSQLを作成することは
難しいことかもしれませんが、本事象のように悪くなった
ケースを覚えたり、調べたことや人に聞いたことを
活用できれば、次の開発では未然に防げるかもしれませんね。
また、No.3の方のオプティマイザヒントの補足をさせていただくと
SQLのヒント句に記述するすることで、オプティマイザの動作を
ある程度制御することが可能です。
補足URL
http://www.asahi-net.or.jp/~QK4Y-ASZW/comp/oracl …
No.3
- 回答日時:
>下記のようなケースで、後者のSQLに変更するとガツンとパフォーマンスが向上したのですが、偶然でしょうか?
偶然です。ですが、良くあることです。
本来、オプティマイザは、ビュー(インラインビュー含む)の検索に対し、検索条件の折り込みを行い、
テーブルを検索しようとします。
判断が適切でない場合に、条件の折り込みが出来ず、実行計画が変化します。
本来は、【もともとのSQL】でも【パフォーマンスが向上したSQL】でも同じでなければいけないのです。
例えば、当方の環境では
select * from emp where empno<7500 and deptno=20
select * from (select * from emp where deptno=20) where empno<7500
の2つは同じ実行計画です。(共にEMPNOによる索引検索)
よって、質問の回答としては、良くある偶然というのが適切です。
【もともとのSQL】は、オプティマイザの実行計画が適切でないだけの話なので、
オプティマイザヒントを書けば、【パフォーマンスが向上したSQL】と同様の結果は得られます。
たぶん、ageやblood_typeに索引が付いていると思うので、その索引を使用することを強要すれば同じになるでしょう。
No.2
- 回答日時:
No1の回答の者です。
改めて質問の先頭を見たらOracleと記述が・・・w
ならば、是非実行計画でSQLを比較してみてください。
時間さえかけてよければ、プログラマーとしては
楽しい作業(のハズ)ですよ。
No.1
- 回答日時:
はじめまして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」から
次の3つの条件
・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'」は血液型でしょうから
日本人が満遍なく登録されているのであれば
全体の4割程度のレコード数でしょうか・・・。
対処方法に関しては是非考えていただきたいのですが
案を1つだけ挙げさせていただけると
Viewから参照するのではなく
各テーブル毎に参照してはいかがでしょうか?
最後に余談ですが、プログラムの動作、発生した事象で
偶然と思えたり、考えられたりする場合
事象発生の要因、原因を知らない
つまり必然であることが分からないという
プログラマーのカンチガイが多いです。
わかりやすい解説、ありがとうございます。
実行計画の取り方を見て勉強します。
また、下記が気になりました。
---
経験則のため一概にはいえませんが、Viewを検索する際に
元となるテーブルのインデックスが使用できるケースと
使用できないケースがあります。
あいまい検索や、フィールドに関数や演算をかましているケースとか以外に、テーブルを直接みれば、インデックスが有効なのに、Viewなら無効になるケースって、どんなときなのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL 書籍の内容はまともでしょうか? 1 2023/01/22 03:07
- Excel(エクセル) PHPプログラムをエクセルに張り付けると検索ボックスがでてくる! 3 2022/05/08 07:10
- Visual Basic(VBA) VBAチェックボックスで有効無効切り替えできるように 5 2022/10/21 16:13
- その他(プログラミング・Web制作) Rでのスクリプトのご相談 3 2022/12/08 16:22
- Visual Basic(VBA) VBA チェックボックスの設定について 1 2022/10/24 10:27
- Oracle 質問です。 下記のテーブルとデータがあり、 取得想定結果のように出力したいです。 下記のsqlだと0 2 2023/05/23 19:10
- 英語 Results: In total, 5,963 titles were identified wi 4 2022/12/29 21:29
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- PHP htmlspecialcharsが機能していないです。 バグですか? 1 2022/04/05 01:22
- MySQL PhpMyAdminで作成して実行せよ。 東京23区を、皇居を中心とした4つのエリア(南東, 南西, 1 2023/06/11 11:58
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
バッチファイルで複数フォルダ...
-
Oracle で文字列からタイムスリ...
-
Object Browser相関タブの見方...
-
SQLについての質問
-
SQLについて教えて下さい。 主...
-
SQLで今日の日付でWhereしたい
-
秋葉原でWindowsを買ったのです...
-
オラクル12C_SQLPlusで実行す...
-
sqlで質問です。 Aテーブルは店...
-
SQLの中上級者へのレベルアップ...
-
SELECTによる表の変換方法を教...
-
開発対象となるアプリケーショ...
-
質問です。 下記のテーブルとデ...
-
Oracleですがsqlで質問です。 ...
-
sqlで質問です。 Aテーブルの登...
-
sqlで質問です。 Aテーブルの情...
-
SQLの書き方について
-
sqlで質問です。 idを元にidに...
-
oracleで 10,20, 30, というデ...
-
索引作成について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
副問合せにLIKE文を使う方法は...
-
データベースのカラムの型がCHA...
-
バッチファイルで複数フォルダ...
-
batファイルでのSQL(oracle)...
-
64bit端末でのOLEDB接続に関して
-
10営業日前の日付を取得したい...
-
osqleditについて
-
update文で質問です。 下記の条...
-
orace SQL文のエラー(ORA-0092...
-
テーブルやカラムの物理名のネ...
-
オラクル12C_SQLPlusで実行す...
-
videopad 無料版 アンインストール
-
SQLの中上級者へのレベルアップ...
-
続.ORACLEのSELECTのソートに...
-
ワークテーブルって何?
-
oracleの直接接続のクライアン...
-
ORA-14452について
-
SQL ブレーク処理について
-
Oracleですがsqlで質問です。 ...
-
oracleで 10,20, 30, というデ...
おすすめ情報