- PR -

Index SeekがScanになってしまう

1
投稿者投稿内容
めだか
大ベテラン
会議室デビュー日: 2004/11/11
投稿数: 109
投稿日時: 2008-04-10 11:35
SQL Server2005
担当者cdは非クラスタ化インデックス

declare @tan nvarchar(3)

select @tan = '114' --条件１
select @tan = '' --条件２ 全件ヒットさせたい

select 担当者cd from r1001 where 担当者cd= @tan --A
select 担当者cd from r1001 where ( 担当者cd= @tan and @tan<>'' ) or (@tan = '' ) --B
select 担当者cd from r1001 where 担当者cd= isnull(@tan,担当者cd) --C

AではIndexSeekだけど空文字での全件ヒットはできない、BCではIndexScanになってしまいます

こういう場合どうしてもSeekにしたければ動的クエリにするしかないのでしょうか？
where句の工夫でSeekにできる書き方あれば教えてほしいです
めだか
大ベテラン
会議室デビュー日: 2004/11/11
投稿数: 109
投稿日時: 2008-04-11 11:29
自己レスですが

試してみた事

一時テーブルを作って@tan=''の場合は全件 @tan='114'の時は'114'をINSERTし
そのテーブルとINNER JOINさせwhere句をはずす
=> 実行計画で比較するとIndex SCANより悪くなった

結局@tan='' の場合とそうでない場合の２つストアド作成し呼び出し元で切り替え
る事にしました

ちなみにコスト比較すると
94% vs 6% でした
かめたろ
ぬし
会議室デビュー日: 2003/03/20
投稿数: 255
投稿日時: 2008-04-11 11:40
引用:

めだかさんの書き込み (2008-04-11 11:29) より:
結局@tan='' の場合とそうでない場合の２つストアド作成し呼び出し元で切り替え
る事にしました

呼び出し元で切り替えるよりは１つのストアドの中でIFで切り替えた方が、と思いました。
好みなので、良し悪しを言っているわけではないので、聞き流してください。
こあら
大ベテラン
会議室デビュー日: 2007/06/26
投稿数: 157
投稿日時: 2008-04-11 12:01
select @tan = '114' --条件１
select @tan = '%' --条件２ 全件ヒットさせたい

select 担当者cd from r1001 where 担当者cd like @tan

とかってダメですか？

[追記]
likeだと色々面倒だし、nullを検索できませんでしたね。
ということで、こっちの方を推してみます。

select @tan = '114' --条件１
select @tan = '' --条件２ 全件ヒットさせたい

select 担当者cd from r1001 where ('' = @tan or 担当者cd = @tan)

と、SQLのチューニングをするときには、
統計情報を採りなおしてからテストした方が良いようです。
[/追記]


[ メッセージ編集済み 編集者: こあら 編集日時 2008-04-11 13:04 ]
めだか
大ベテラン
会議室デビュー日: 2004/11/11
投稿数: 109
投稿日時: 2008-04-11 14:20
>かめたろさん
ストアド内で実行計画に影響するようなIF文はあまりよくないのかな？と思って呼び出し元からわけました

>こあらさん
select 担当者cd from r1001 where ('' = @tan or 担当者cd = @tan)
統計情報を採りなおしてやってみたのですが当方の環境ではscanになってしまいました

アドバイスありがとうございました
SQL Server2008ではオプティマイザ賢くなってたりしないのでしょうか？
あとオラクルではどうなるのかご存知ないでしょうか
1

スキルアップ／キャリアアップ（JOB@IT）