該当データが存在しないときに、全件検索を回避するには?

質問

たとえば、以下のA01KINGAKUテーブルがあったとして
KAISHA CHAR(2) (主キー)
KINGAKU NUMBER(15)

このテーブルに1万件のデータが入っていて
各レコードのKAISHA項目は、全て 'A1'
だったとしたとき

Select * from A01KINGAKU where KAISHA = 'A2'
としたときに、
インデックスをみれば、A2が存在しないというのは
すぐにわかりそうなものですが、
Oracle 10gで、これをしたら、全件検索されてしまいました。
（統計情報は直前に取得済みです）

たぶん、インデックスの値に散らばりがないので
インデックスを使わないという判断になったのだと思いますが
インデックスを強要する術はないでしょうか？
/*+index(A01KINGAKU インデックス名)+*/ とすればよいのでしょうが
こうしたことを多数のテーブルについてやりたく
いちいちインデックス名を調べるのがわずらわしく
インデックス名がいらないヒントの方法あるいは
ヒントとはまったく違った方法を探しています。

(そもそもそんなもの主キーにするなとか、質問の意図が読めないというご意見もあるかと思います。
元は、ALLKINGAKUテーブルという複数のKAISHAコードを扱うテーブルがあって、これを処理速度向上のため、
KAISHAコードごとにテーブルを分割し、
ALLKINGAKUテーブルを廃止して、代わりに
select * from A01_KAISHA union all select * from A02_KAISHA ...
としたALLKINGAKUビューを作成したと思ってください。
この時、Select * from ALLKINGAKU where KAISHA = 'A02'としたときに、ビュー上で、A01_KAISHAまで全件検索されてしまったことに
端を発している質問です）

回答No.1

joih · ベストアンサー

うーん、混乱する質問ですね。整理すると、
(1)ALLKINGAKUビューが下記のとおり作成してある。
　Select * from A01KINGAKU
　union all
　Select * from A02KINGAKU
　union all
　Select * from A03KINGAKU

(2)A01KINGAKU、A02KINGAKU、A03KINGAKUテーブルにはそれぞれ
KAISHA項目があり、インデクスが貼ってある。

(3)このビューに対して
　Select * from ALLKINGAKU where KAISHA = 'A2'
　と検索したら
　A01KINGAKUテーブルを全件検索していた気がする。
　A01KINGAKUテーブルを全件検索してないと思うには、
　または該当するテーブルだけを検索するようにするにはどうしたらいいですか？

という質問ですか？

パーティションテーブルを使ったらどうでしょうか。

SQL実行計画は見ましたか？
全件検索(TABLE ACCESS FULL)ではなく索引検索(INDEX RANGE SCAN)になっていませんか？

補足

コメントありがとうございます。

はい、質問の意図はご指摘の通りです。
A01KINGAKU, A02KINGAKU , A03KINGAKUすべてが全件検索されています。
実行計画は確認しております。
パーティションテーブルは、Enterprise以上でないと使えないので
このようなテーブル構成を自作しているようです。

回答No.2

SUPER-NEO

こんにちは。

インデックスが意図した形で機能してくれない、
ということなんですが、オラクルでインデックスが機能しない
ケースというのはご存知でしょうか？
オラクルでは全レコード数の確か５％程度の結果が得られる場合に、
インデックス検索を行うというそうです。
つまり、極端な話ですと、１万件の中から9999件のレコードを
取得するのに、いちいちインデックスを使うよりも、
テーブルを総なめしたほうが高速である、ということです。
オラクルはこれを自動的に判断しています。

提示された例で考えると、テーブル内には１万社分のレコードが
登録されていて、テーブルに存在しないキーを条件につけた場合、
ということでの相談なっていますが、ただ単にレコードの有無だけを
判断するのでしたら、COUNT(キー項目)をとるのが良いと思います。
「＊」を使うと、カーソルを使用したり、不要なフィールドへの
アクセスが発生し、パフォーマンス的にも悪影響を及ぼします。
ですので、キー項目のカウントを取ることによって、
全体のパフォーマンスは向上すると思いますよ。

＃１さんへの回答にありますように、索引の全体検索となっている
だけですと、それが現状の最高パフォーマンスといえますが、
全表検索が走っているようであれば、そのテーブルの設計に問題が
あるようにも感じられます。

現状１万件程度のデータで、どれくらいのパフォーマンスを発揮して
いるのでしょうか？
私も色々とチューニングを経験してきましたけど、最もボトルネックが
潜んでいるといえるのが、実は実行する SQL 文だったりします。

あと最後にアドバイスですが、ビューは SQL 文を見やすくはしますが、
落とし穴になりがちです。
実際、SQL を実行した際には最初に実行されるのがビューの部分です。
ここで巨大な２つのテーブルを JOIN しているようなら、
そこがボトルネックになります。
２つのテーブルを、その段階で全表検索します。