以下のようなSQLについて教えてください。

売上トランと商品マスタがあるとします。
商品マスタは廃止フラグをVARCHAR2(1)で保持していて、
1なら廃止、0なら今販売中で、
商品マスタは全件で100万件あり、そのうち95万件は既に廃止になっているとします。

このとき、

SELECT 売上トラン.* , 商品マスタ.商品名称
FROM 売上トラン
INNER JOIN 商品マスタ
ON 売上トラン.商品コード = 商品マスタ.商品名称
WHERE 商品マスタ.削除フラグ = '0'

と書くのと、

SELECT 売上トラン.* , 商品マスタ.商品名称
FROM 売上トラン
INNER JOIN (SELECT * FROM 商品マスタ WHERE 商品マスタ.削除フラグ = '0') 商品マスタ
ON 売上トラン.商品コード = 商品マスタ.商品名称

と書くのだと、検索は下の方が速くなったりしますか？

イメージですが、
上だと全件同士でくっつけた後で削るのに対して、
下だと削って5万件だけになったものをっつけていて、下の書き方の方が良いのかな？
と思ったものの、どこかの本などでこういう書き方が良いと読んだわけではないので、
イメージであっているのか、それとも変わらないのか質問しました。


また、削除フラグのように1か0の2値しかとらない項目については、
INDEXを張る効果はあるでしょうか？


ちなみにverで挙動が変わるかわかりませんが、環境はoracle11gを想定した場合となります。

No.4 回答者： entree 回答日時： 2015/01/09 23:19

> 検索は下の方が速くなったりしますか？

統計情報が正しく取られているなら速くはならないでしょう（同じということ）。
売上トランの件数にもよりますが(※)、どちらも商品マスタを削除フラグ = '0'
で絞ってから結合という実行計画になると思われます。

ただし、上の記述の方がシンプルなのでおすすめします。複雑な記述はオプティ
マイザを惑わせたり、オラクルの不具合を引く確率を高めたりするので。

※絞り込んだ後も商品マスタの件数が５万件と大きめなので、例え売上トラン.
　商品コード列に索引があったとしても、ハッシュ結合が選択される可能性があ
　ります。

> また、削除フラグのように1か0の2値しかとらない項目については、
> INDEXを張る効果はあるでしょうか？

例え２値しかなくても、1/20に絞り込まれるわけですから大いにあります。バイ
ンド変数を使用していないならなおさらです。最近のオラクルは賢いのでテーブ
ルのどの列が検索条件 (WHERE 句等) で使用されるかモニタリングし、検索に使
用される列だけヒストグラム統計を取ってくれるようになっていますが、自動的
に取ってくれない場合は手動で取る必要があります。

No.3 回答者： pringlez 回答日時： 2014/12/31 14:25

もしかしたら同じアクセスパスになるかもしれません。

しかしこの2つのSQLであればおそらくはあなたの想像通りに
・1つめだったら結合して、大きなテーブルを作ってから絞込み、
・2つめだったら商品マスタを絞ってから結合
になると思います。

しかし確かなことは実行しなければ分かりません。例えば同じSQLでもレコード数によってもアクセスパスは変わります。また、同じSQL・同じレコード数でも、実行時間が長いときにはより最適な方法を探そうとしてアクセスパスが変わることもあります。導入当初と運用中にアクセスパスが変わるのはよく発生することなので、「導入したときはこうだったんだけど…」といわずに毎週もしくは毎月のようにチェックをしたほうがいいです。とにかく想像して終わりなのではなく、STATSPACKをとるなどして実際のアクセスパスを確認するようにしましょう。


というか普通に

SELECT 売上トラン.* , 商品マスタ.商品名称
FROM 売上トラン
INNER JOIN 商品マスタ
ON 売上トラン.商品コード = 商品マスタ.商品名称
AND 商品マスタ.削除フラグ = '0'

とすればいいのに、なぜ結合してから絞るの？と思ってしまいます。


>また、削除フラグのように1か0の2値しかとらない項目については、
>INDEXを張る効果はあるでしょうか？

この検索の仕方でかつ、100万件のデータでかつ、取得するのが5万件なら確実に効果はあります。そして確実に先に絞り込むためにあなたの2つ目のSQLか、私の書いたSQLにしてみましょう。これこそ実測して確かめたほうがいいですよ。どのくらい効果があるのか理解できるでしょうから。

あと、お勧めでいうと、廃止したデータがそんなに多いのなら、廃止データは別テーブルに移動したほうがいいですよ。通常は有効な商品を使った検索が多いのでしょうから。そうすればインデックスを使うよりはるかに速く検索できます。で、廃止もふくめて検索する場合には2つのテーブルをUNIONするのはどうでしょう。

No.2 回答者： shitaba 回答日時： 2014/12/29 22:04

統計情報がきちんと取られているのなら、おそらくどちらも

変わらないと思います。

上のSQLの場合『全件同士でくっつけた後で削る』動きになる
とは限りません。『くっつける』ほうが先か『削る』ほうが先
かは、ORACLE が自動的にどっちが効率的かを判断して決め
ます。このケースでは 商品マスタ.削除フラグ = '0' の件数が
明らかに少ないので、おそらく『削る』ほうが先に動くかと
思います。

『ORACLE が自動的にどっちが効率的かを判断して決める』
機能を『オプティマイザ』といい、オプティマイザが決めた
処理順序を『実行計画』と言います。どちらもDBのパフォー
マンスを語る場合には避けて通れません。
SQL の効率の良さは実行計画を比べると判ります。もし、
実際の ORACLE 上で問題の2つの SQL を実行できるのであ
れば、以下のページを参考に実行計画を取得してみましょう。
http://d.hatena.ne.jp/replication/20130829/13778 …

Cost や Time の項目から効率を見ることができます。
# この機能を使えば、遅くて困っているSQLの原因を調べるこ
# とも可能です。

No.1 回答者： bin-chan 回答日時： 2014/12/29 19:58

やってみないと、ではあるがフラグ判定してJOINの方がよさそう。

> 削除フラグのように1か0の2値しかとらない項目については、
ビットマップインデックスでしょうね