プロが教えるわが家の防犯対策術!

あいまい検索(部分一致)検索をする際に、あいまい検索だとインデックスも効かないので、インデックスを張ってパフォーマンス向上という訳にもいかず、パフォーマンスが非常に悪くて困っています。
状況としては、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に対しての検索で実施しました。

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

このQ&Aに関連する最新のQ&A

A 回答 (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/
    • good
    • 0

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


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

経験したことで満足に検証していないため
原因であるとは断定できませんが・・・。
ビューの元となるテーブルが別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 …
    • good
    • 0

>下記のようなケースで、後者の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に索引が付いていると思うので、その索引を使用することを強要すれば同じになるでしょう。
    • good
    • 0

No1の回答の者です。


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

はじめまして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から参照するのではなく
各テーブル毎に参照してはいかがでしょうか?

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

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

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

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

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

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QLike文の速度について教えてください

どなたかSQLについてわかる方教えてください。

以前にLike検索は全件検索だから遅いというのを聞いたのですが、

Selectの条件としてWhereに「Like "A%" and Like "B%"」と設定した時と
「Like "A%"」と「Like "B%"」とを設定した処理を2回に分けて検索した時とでは
の検索処理速度的にどちらの方が早いのでしょうか?
あと、サーバの負荷はどちらの方がかかるのでしょうか?

よろしくお願いします。

Aベストアンサー

前方一致ならインデックスが効くと思います。
当然、DBMSがそのインデックスを使用する実行計画を選択した場合ですが。

2回SELECTした場合はディスク走査が2回になりそうです。
厳密に言えば、クエリーのパース時間も2回分になるでしょう。

LIKE 'A%' OR LIKE 'B%'の場合はディスク走査はおそらく1回で済みそうです。

キャッシュやバッファ等もあるので、2回実行するからといって、純粋に時間も2倍とはならないでしょうが、LIKE条件をORで結合したほうが速そうな印象はあります。

いずれにしても、現在のサーバー性能で数万件程度のデータ操作なら、大差はないと思います。
ただし、ループ内で実行する等、高頻度で実行するのなら、小さな差も無視できません。

どうしても白黒つけたい場合、想定データ量も分かっているようですし、自分ならテストデータを作って試してみます。一応の根拠にはなりますよね。

QMAX値を条件にデータを取得するには?

SQL文で困っています。
ご教授下さい。


下記のようなデータがあった場合、それぞれの区分毎に
年月が最大(最新)のデータを取得したいです。
(実際には1レコードにその他項目があり、それらも取得します。)
<検索対象データ>
区分 年月   金額
-----------------------------
A   200412  600
A   200503  560
B   200311  600
B   200508  1000
B   200504  560
C   200508  400
C   200301  1100


<取得したいデータ>

区分 年月   金額
-----------------------------
A   200503  560
B   200508  1000
C   200508  400

よろしくお願いします。

Aベストアンサー

テーブル名をXXXとすると次のようなSQLでよいと思います。(最善の方法かどうかは自信がないですが)

select B.* from (select 区分, max(年月) as 年月 from XXX group by 区分) As A
inner join XXX as B on A.区分 = B.区分 and A.年月 = B.年月
order by B.区分

QViewにインデックスは張れますか?

件名の通りなのですが、作成したViewが遅くて困っています。
改善方法としてはViewを作成しないで従来のSQLにインデックスを張って取得する方法にしようかなと考えています。
なにかいい方法はありますか?

Aベストアンサー

Viewの元テーブルに適切なIndexを貼る、ではいけないのですか?

QOracle(オラクル)で、日付時刻型の検索方法について

質問させていただきます。
データベースはオラクルを使っていて、
SQL文で、抽出するときにエラーが出て困っています。

日付時刻型が「2005/05/26 19:13:00」という感じで入ってます。
2005/05/26 を抽出したいのですが、
BETWEEN '2005/05/26 00:00:00' AND '2005/05/26 23:59:59'

だと、エラーでできません。
どなた様か、ご教授よろしくお願いしますm(_ _)m

Aベストアンサー

日付検索を行う場合は、以下のように書式を含める必要があります。

col BETWEEN TO_DATE('2005/05/26 00:00:00','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('2005/05/26 23:59:59','YYYY/MM/DD HH24:MI:SS')

ただ、厳密には

col >= TO_DATE('2005/05/26', 'YYYY/MM/DD')
AND
col < TO_DATE('2005/05/27', 'YYYY/MM/DD')

と書くべきでしょうね。

Q実行計画の「COST」と「BYTE」について教えていただきたいです。

実行計画の「COST」と「BYTE」について教えていただきたいです。

書籍には
COST・・・・CBOによって見積もられた操作コスト。
BYTE・・・・アクセスされるバイト数のCBOのアプローチによる見積もり。
と書かれていますが、いまいちピンときません。


私は、
COSTは、検索するテーブルのデータ量が多いほうがコスト値が大きくなる。
BYTEは、検索条件に合致して取得できるデータが多いほうがバイト値が大きくなる。
と思っているのですが、正しいでしょうか?

Aベストアンサー

このあたりを参考にしてください。
COSTはデータ量だけではなく、その表やViewのアクセスに要する時間やSortや結合が必要なら、そのために必要なCPU時間等も考慮されています。
表があるHDDのアクセス速度なんかも考慮されているし、表のエクステントが複数になっているかとかも考慮されています。
書籍はわかりにくいかもしれませんが、嘘は少ないと思います。著者が思い違いをしてないとは言い切れませんが。

参考URL:http://otn.oracle.co.jp/forum/message.jspa?messageID=35016743

QSQLで特定の項目の重複のみを排除した全項目を取得する方法

私は仕事上でデータベースを扱っていて、タイトルのような処理を行う必要があるのですが、いかんせん方法がわからずネット上を検索しても同様だったためここで質問させていただきます。

質問点を簡単に説明いたしますと、
たとえばAというテーブルがあって、

項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 A    い    ア    以
 A    う    ア    宇
 B    え    イ    江
 B    お    イ    尾

上のような構造になっている場合に「項目名1」について重複している項目を排除し、結果として


項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 B    え    イ    江

上のようなデータを取得したいのです。
この時に、Aの重複を排除して取得するレコードは1~3行目のどれでもよいです。
また、データを取得する際には必ずそのレコードの「全項目」を取得したいのでDistinctはうまく使えませんでした。

どなたか詳しい方、方法を教えてくださると幸いです。回答お待ちしております。

私は仕事上でデータベースを扱っていて、タイトルのような処理を行う必要があるのですが、いかんせん方法がわからずネット上を検索しても同様だったためここで質問させていただきます。

質問点を簡単に説明いたしますと、
たとえばAというテーブルがあって、

項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 A    い    ア    以
 A    う    ア    宇
 B    え    イ    江
 B    お    イ    尾

上のよ...続きを読む

Aベストアンサー

比較可能で一意性のある値をもてる項目6をテーブルに追加して、

select T.* from T, (select Item1,min(Item6) as Item6 from T group by item1) W where T.item6=W.item6;

――ってやるのが、一番手っ取り早いと思います。
他のところに影響がでないのであればですが。
oracleならrowidを使うとか、レコードの更新時刻を突っ込むとか。

QOracleで流したSQLのログを取得できますか?

ASP+Oracle9iで作られたシステムがあります。
Oracleの設定などは前任者がしており、まともな引継ぎを受けないまま私が維持運用員となりました。
現在DBからあるレコードが消えてしまい(最初から作られてない可能性もあり)、顧客からクレームがついています。
状況から考えて怪しいプログラムを調査したのですが、原因不明でした。
そこで、Oracleの方で今まで流れたSQLをログとして保存していれば手がかりがつかめるのではないかと思い、情報を探しているのですが見つからないので教えて頂きたいのです。
私はOracleの知識(管理面について)が殆どなく、Oracleを使うことはできるが、Oracleの設定がどういう状況になっているか調べることもできず、資料もありません。

1.そもそもOracleにSQLをログとして保存する機能があるのか?
2.あるとすれば、保存機能が有効になっているか、保存したファイルがどこにあるかをどこで確認できるか?

以上の点についてご存知の方、どうぞ回答よろしくお願いします。

Aベストアンサー

OracleのSQL実行痕跡は以下に示すものの中にあります。
ただし、それぞれの設定レベル、保存方法によって、参照できる範囲は変わります。

1.オンラインREDOログとアーカイブログ
  オンラインREDOログはすべてのOracleデータベースに存在します。
  ですが、そのREDOログのサイズとシステムのアクセス量によって保存期間は変化します。アーカイブログ設定しておけば、古いREDOログはアーカイブとして吐き出されます。
  これらは#1の方がおっしゃったLOGMINERで実行済みSQLを調査することができます。
  アーカイブログモードになっているかどうかは、下記で確認できます。
   SQL> select log_mode from v$database;

  また、どのアーカイブログファイルを調査するかは下記を参照すれば良いと思います。
   SQL> select name,to_char(completion_time, 'yyyy/mm/dd hh24:mi') adate from v$archived_log;

2.フラッシュバック問い合わせ
  これは初期化パラメータUNDO_MANAGEMENTがAUTOに設定されており、かつUNDO_RETENTIONが保存期間として適切に設定されている場合のみ使用できます。
  やっていることは、UNDO表領域(旧RBS用表領域)の中に残っている実行履歴を参照しています。したがって、データベース設計時にフラッシュバック問い合わせを前提としていない限り、使える可能性は低いです。

3.その他
  監査を実行していれば、実行SQLを取得できる可能性があります。
  ただし、監査レベルなどを理解しておかないと、すべてのSQLが取得されていない可能性がありますので、難しいかもしれません。

OracleのSQL実行痕跡は以下に示すものの中にあります。
ただし、それぞれの設定レベル、保存方法によって、参照できる範囲は変わります。

1.オンラインREDOログとアーカイブログ
  オンラインREDOログはすべてのOracleデータベースに存在します。
  ですが、そのREDOログのサイズとシステムのアクセス量によって保存期間は変化します。アーカイブログ設定しておけば、古いREDOログはアーカイブとして吐き出されます。
  これらは#1の方がおっしゃったLOGMINERで実行済みSQLを調査することができま...続きを読む

Q3つの表の外部結合

表A、B、Cの3つがあり、Aのすべての行を出力したいと考えています。
外部結合を用いるのだとは思うのですが、3つの表に対して行う場合の
書き方がわからず困っています。
ご教授いただけないでしょうか?
select * from a,b,c
where a.商品ID =b.商品ID (+) and b.商品ID (+) =c.商品ID (+)
としてみましたが、うまくいきませんでした。

Aベストアンサー

ansi構文の趣旨からいえば、結合条件と絞り込み条件は分けて書くので・・

select *
from a
left join b on (a.商品ID =b.商品ID)
left join c on (b.商品ID =c.商品ID)
where a.年月 = 任意の値

と書くのが一般的でしょうね。

Qlikeとsubstrの使いわけについて

現在、大量データを扱うマスタDB(oracle)に対し、オンライン業務にて曖昧検索を実施する機能を開発しています。
性能を十分に考慮したsqlを組む必要があり、以下のいづれを選択するか判断しかねています。
それぞれの意味合いを十分に理解できておらず申し訳ございませんが、どなたかご教示をお願いします。
■要件
 曖昧検索をして検索を行う。
 例)漢字氏名が”林”で始まるお客様を一覧表示
■実現するためのSQL文
 案1)select~ where 漢字氏名 like ’林%’; 
 案2)select~ where substr(漢字氏名,0,2)=’林’;
 ※漢字氏名の型はvarchar2です。

処理時間(oracleのコスト的には)どちらも変わりませんかね?
以上、宜しくお願いいたします

Aベストアンサー

漢字氏名列に索引がある場合、LIKE検索では、レンジスキャン可能です。
substrなど関数を介す場合は、一般的には索引を用いません。
ただし、substr関数を使うことが分かっている場合は、ファンクション索引を
使うことでファンクション索引に合致する検索であれば索引が利用できます。
仮に索引がない場合、LIKEでもsubtr関数でもフルスキャンですから、
substr関数のオーバーヘッド程度の違いしか生まれません。

索引の有無で変わってくるので、実際にどちらが効果的かは、実行計画を自ら比較してください。

Q「いずれか」と「いづれか」どっちが正しい!?

教えて下さいっ!
”どちらか”と言う意味の「いずれか」のかな表記として
「いずれか」と「いづれか」のどちらが正しいのでしょう???

私は「いずれか」だと思うんですが、辞書に「いずれか・いづ--。」と書いてあり、???になってしまいました。
どちらでもいいってことでしょうか?

Aベストアンサー

「いずれか」が正しいです.
「いづれ」は「いずれ」の歴史的かな遣いですので,昔は「いづれ」が使われていましたが,現代では「いずれ」で統一することになっていますので,「いずれ」が正しいです.


人気Q&Aランキング