どんなインデックスを設定したら良いでしょうか？

質問

お世話になっております。
Windows2003Server
SQLServer2000(SP4)
を使用しています。

新しく作成したテーブルに、どのようなインデックスを設定したら良いか悩んでいます。

新規作成のテーブル：tableA
カラム　　　　　主キー　型　規定値　説明
---------------------------------
ユニークNo　　　P　　　INT　　　　　ユニークなコード
大分類コード　　P　　　INT　　　　　大分類マスタとリンクする
中分類コード　　P　　　INT　　　　　中分類マスタとりんくする
代表フラグ　　　　　　　INT　　0　　 0と1があり、1の方が優勢

やりたいこと：
tableAからユニークNoが重複しないようにしてデータを取得する。
このとき、ユニークNoが重複している場合は、代表フラグ=1のデータを取得する。
ただし、代表フラグ=1のデータが複数存在する場合は、大分類コード、中分類コードの最若番のデータを取得する。
また、代表フラグ=1が0件で、代表フラグ=0が複数ある場合は、その中から、大分類コード、中分類コードの最若番のデータを取得する。

これを実現するために作成した取得用ストアド：
SELECT
　　x.ユニークNo,
　　x.大分類コード,
　　中分類コード,
　　x.代表フラグ
FROM
　　(SELECT
　　　　ユニークNo,
　　　　MAX(代表フラグ) AS 代表フラグ
　　FROM　[tableA]
　　GROUP BY　ユニークNo) AS z,

　　(SELECT
　　　　ユニークNo,
　　　　代表フラグ,
　　　　MIN(大分類コード) AS 大分類コード
　　FROM [tableA]
　　GROUP BY ユニークNo,代表フラグ) AS x,

　　(SELECT
　　　　ユニークNo,
　　　　代表フラグ,
　　　　大分類コード,
　　　　MIN(中分類コード) AS 中分類コード
　　FROM　[tableA]
　　GROUP BY　ユニークNo, 代表フラグ,大分類コード) AS y
WHERE
　　x.ユニークNo = y.ユニークNo AND
　　x.大分類コード = y.大分類コード AND
　　x.代表フラグ = z.代表フラグ AND
　　y.代表フラグ = z.代表フラグ AND
　　x.ユニークNo = z.ユニークNo

アドバイス、宜しくお願いします。

回答No.3

tomo316 · ベストアンサー

>WHERE
>　　x.ユニークNo = y.ユニークNo AND
>　　x.大分類コード = y.大分類コード AND
>　　x.代表フラグ = z.代表フラグ AND
>　　y.代表フラグ = z.代表フラグ AND
>　　x.ユニークNo = z.ユニークNo
これは副問い合わせ（インラインビューー、仮想ビュー）の条件なのでインデックスは効きません。
＞GROUP BY　ユニークNo) AS z,
＞GROUP BY ユニークNo,代表フラグ) AS x,
＞GROUP BY　ユニークNo, 代表フラグ,大分類コード) AS y
GROUP BYは内部でソートするのでインデックスををつけると有効です。
ユニークNo
ユニークNo,代表フラグ
ユニークNo, 代表フラグ,大分類コード
の3つを作ればどうですか。

p.s
複雑なSQLになると言うことは構造が悪いと言うことです。
ANo.1さんが言ったように構造の見直しをしたほうがいいと思います。
これを、テーブルの正規化といいます。
正規化は自動ツールもあるので一度試してみれば。
アクセスでもできますよ。

お礼

回答ありがとうございます。

>これは副問い合わせ（インラインビューー、仮想ビュー）の条件なのでインデックスは効きません。
>GROUP BYは内部でソートするのでインデックスををつけると有効です。

勉強になりました。
ありがとうございました。

>複雑なSQLになると言うことは構造が悪いと言うことです。

なるほど...

>ANo.1さんが言ったように構造の見直しをしたほうがいいと思います。

上の方が構造を考えて、下の者が苦しむと言った事が普通に行われている、ひどい部署です。
まして、一旦動き始めてしまった物の構造を変えるなんて不可能な、どうしようもない人間の集まりです...

回答No.2

kuroooooo

昨日の回答の訂正と補足です。

>存在しないデータは取りに行かないです。（検証済み）

すみませんでした。where句の読み違えでした。取りに行きませんでした。

＞こうした場合、代表フラグ=0と1と合計2レコードしか作れないので、これもダメです。

代表フラグがintだったのでいくらでもいけると思ったんですが・・・
この方法の場合、代表になりうるレコードは常に０固定とかにして、1以降を枝データとして扱えるというやり方になります。
もう少し詳しく言えば、あるデータを代表10番で追加します。これが
代表になりうる条件を満たしていたときは0番を10番の内容で上書きします。0と10として2レコード存在することになりますが、
代表だけとりたいときはwhere句で代表0を指定するだけですみます。
意味合い的には「代表フラグ」というよりは「枝番」という表現に
なると思います。結果selectも早くなります。

ただ、どうしても今の構造を崩さず使うことが目的であれば、
データ量・サーバースペックにもよりますが主キーのみで、
どうしてもパフォーマンスが悪ければ、「ユニークNo,代表フラグに
インデックスを張る」でしょう。

お礼

回答、ありがとうございました。

＞代表フラグがintだったのでいくらでもいけると思ったんですが・・・

すみません。説明が足りなかったです。
代表フラグはintですが、値は0,1のみなんです。

今回は、今の構造を崩さず使うことが目的で、その上で最適なインデックスを設定したいのですが、回答者様のお話、大変参考になりました。
ありがとうございました。

回答No.1

kuroooooo

ざっと見ただけですが・・・
このselect文(積)だと存在しないデータを取りに行くような気がします。
次の2つのデータの場合
ユニークNo 1, 1 ->group 1
大分類 1, 2 ->min 1
中分類 2, 1 ->min 1
フラグ 1, 1 ->max 1
あと主キーも重複するのでは？

個人的には可能であればテーブル（データ）構造の見直しをお勧めします。
１．ユニークNoは重複無しにする
　主キー：ユニークNo
インデックス：大・中分類
２．重複するのであれば、代表フラグは重複しないようにする。
　主キー：ユニークNo、代表フラグ
インデックス 大・中分類
とかですね。
新規にデータを入れるのであればデータ構造はシンプルにしておいたほうが問題の発生が少なくなります。

補足

回答ありがとうございます。

>このselect文(積)だと存在しないデータを取りに行くような気がします。

存在しないデータは取りに行かないです。（検証済み）

>あと主キーも重複するのでは？

これはちょっと意味が分からないのですが・・・
取得結果が重複するのでは？と言うことでしょうか？
それは大丈夫です。これも検証済みです。

あと、ユニークNoに対して、複数の大分類、中分類を持つことが目的なので、
>１．ユニークNoは重複無しにする

は、ダメです。
また、

>２．重複するのであれば、代表フラグは重複しないようにする。

こうした場合、代表フラグ=0と1と合計2レコードしか作れないので、これもダメです。

他に何かアドバイスがありましたら、お願いします。