こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

WHERE句のSUBSTRINGを置き変えたい

sqlserver 2008環境です。

WHERE SUBSTRING(TestCD, 9, 4) <> '0000'

上記のように、20桁の文字列コードをSUBSTRINGしている箇所があります。
インデックスが効かないので、LIKEか何かに置き変えられないかと考えて
いるのですが、うまい方法が思い浮かびません。
良い書き方があれば教えていただけないでしょうか。

投稿日時 - 2016-07-15 12:18:10

QNo.9201901

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.4

まだ締まっていなかったので、回答しました。

まず、前の回答者様からもありましたが、質問の内容では、LIKEにしてもインデックスを利用することはできません。
インデックスは列情報を加工した場合には無視されるためです。
ここら辺は、インデックスの仕組みを勉強すると理解できるかと思います。

また、前の回答者様と同様、一番は、設計の見直しですが、それも難しい、、、ということであれば、
計算列を作成し、インデックスを貼るという方法があります。

これであれば、現行のテーブルに列を追加、インデックスの追加だけですので、
既存のシステムへの影響はかなり抑えられます。

今回であれば、

1.CalcKey という計算列を作成(この中には、SUBSTRING(TestCD, 9, 4)の値が格納されます。)
ALTER TABLE CalcKey ADD テーブル名 AS (SUBSTRING(TestCD, 9, 4))

2.CalcKey をキーとしたインデックスを作成する。
CREATE NONCLUSTERED INDEX TEST_INDEX ON テーブル名 (CalcCol)

検索時は、以下のようになります。
WHERE CalcKey <> '0000'

計算列の内容は、自動的に切り替わるため、システムからの更新等は必要ありません。

参考URL:https://msdn.microsoft.com/ja-jp/library/ms188300(v=sql.120).aspx

投稿日時 - 2016-08-10 23:56:12

ANo.3

>インデックスが効かないので、LIKEか何かに置き変えられないかと考えているのですが

LIKEに書き換えてもインデックスが効かないのは変わらないと思います。

テーブル構造をを書き換え、TestCDフィールドを

TestCDの1文字目から8文字目
TestCDの9文字目から12文字目
TestCDの13文字目以降

の3つのフィールドに書き換えた方が早いです。

TestCDフィールドを3つに分離して、TestCD1、TestCD2、TestCD3と言う3つのフィールドにテーブルを改造します。

改造したら、テーブルを別の名前に変更し、CREATE INDEXなどでインデックスを作成します。

そして「元のテーブルと同じ名前で、クエリを新規作成」して

SELECT ~~~~,TestCD1+TestCD2+TestCD3 AS TestCD,~~~~
FROM 改造したテーブル

というクエリを作ります。

このクエリは「改造前のテーブルと同じ名前になっていて、改造前のテーブルと同じ結果を返す」ので、改造前のテーブルを参照している他のクエリを修正する必要はありません。

で、

SELECT ~~~
FROM 元のテーブル名
WHERE SUBSTRING(TestCD, 9, 4) <> '0000'

とやってるクエリを

SELECT ~~~,TestCD1+TestCD2+TestCD3 AS TestCD,~~~~
FROM 改造後のテーブル名
WHERE TestCD2 <> '0000'

に変更して下さい。インデックスが効いた状態でSELECTする筈です。

という訳で「テーブルの設計そのものに問題があるので、テーブルの設計を見直すべき」だと思います。

投稿日時 - 2016-07-15 12:51:02

ANo.2

create index インデックス名 on テーブル名 ( SUBSTRING(TestCD, 9, 4))

ができませんかね?
実験してないので責任はもてませんが。

投稿日時 - 2016-07-15 12:42:39

ANo.1

どんなテーブルか、リレーションかに寄りますがインデックスビューの作成可能範囲であればインデックスビューを作成すればよいだけでは?

投稿日時 - 2016-07-15 12:38:18

あなたにオススメの質問