Q&A
ご回答ありがとうございます。
今回の抽出条件に対してテーブルの設計やIDの採番方法について改善の余地があることは理解しておりますが、システム構築時 からは携わってきていないので、設計思想についてはまだ理解が及んでおりません。
テーブル設計の定石など、とても勉強になりました。ありがとうございました。
SQLの性能について質問です。
Oracle 11gにて、抽出条件が『ID(varchar2)の下一桁が「1」でないレコード』というSQLを作成しています。(IDは索引付きのカラムです。)
仕様をそのままコーディングすると
WHERE NOT SUBSTR(ID,-1) = '1'
となると思いますが、
NOTにより索引走査ではなく全表走査となると認識しています。
そのため性能の良いSQLを検討中ですが、
REGEXP_LIKE(ID,'[^1]$')
とした場合、こちらは索引走査されるでしょうか？
LIKEはワイルドカードの前までは索引走査されると聞いたので、そうするとREGEXP_LIKEも全表走査されると予想していますが、情報がなく困っています。
もし結局性能が変わらないようであれば、NOT SUBSTRの方が可読性が高いので採用したいと思っています。
もし他に良い条件式がある場合はご教示願います。
ご回答よろしくお願いいたします。
回答4件
0
ベストアンサー
文字列の列に使われるような索引は、通常Bツリー形式ですが、これは仕組み上先頭一致、あるいは連続した範囲の検索にしか使えません。
「最後1文字だけ」のような関数インデックスがあるのなら格別、通常のBツリーインデックスのみの環境では、テーブルスキャン以外の手段はありません。
投稿2017/11/30 07:00
総合スコア146543
0
他の人も書いているようにファンクションインデックスを使う方法がありますが、「1つの列には1つの情報しか持たせない」というテーブル設計の定石があります。
今さらテーブル設計を変更できないという事情があるかもしれないですが、列の最後の1桁を別の列として設計するべきではなかったでしょうか？
投稿2017/12/01 01:41
総合スコア16417
0
仕様をそのままコーディングすると
WHERE NOT SUBSTR(ID,-1)
となると思いますが、
ではなく、
SQL
1WHERE NOT SUBSTR(ID,-1)='1'
ですね。
NOTにより索引走査ではなく全表走査となると認識しています。
**SUBSTR(ID,-1)**というファンクションインデックスが作成されているならそうですが、
IDのみのインデックスなら、NOTが無くとも演算した時点でインデックスは適用されません。
多分意図するところは、
SQL
1WHERE ID in (SUBSTR(ID,1,LENGHT(ID)-1) || '2', SUBSTR(ID,1,LENGHT(ID)-1) || '3', ・・・)
のような記述ではないでしょうか。
投稿2017/11/30 14:30
総合スコア25428
ご回答ありがとうございます！
記載ミスのご指摘ありがとうございます。
SUBSTRで索引走査がしたいのではなく、可読性・保守性と性能のバランスが良いSQLを作りたく試行錯誤しております。
ご教示いただいた条件式だと記載がかなり長くなりますが、どの程度性能が上がるのでしょうか。
どの程度性能が上がるかは、前提として抽出対象のカーディナリティが高くインデックスの適用があることによります。
カーディナリティが高いなら、SUBSTR(ID,-1)によるファンクションインデックスを適用し、
WHERE SUBSTR(ID,-1) in('2','3',・・・)
とするのが、一番良い気がします。
また、末尾の範囲が0~9なら、CAST(SUBSTR(ID,-1) as number(1)) -1 のファンクションインデックスとして、
WHERE CAST(SUBSTR(ID,-1) as number(1)) -1 > 0
の方が、記述は多少短くなります。
ですが、結局はカーディナリティ次第なので。末尾の範囲が0~9で分布が均等なら、全表検索後のフィルター以上にはならないので改善はあまり見込めないと思います。
ありがとうございます。
とても勉強になりました。
やはり今回は最初の方法を採用いたします。
母数が長大なら１０分の１でも他の項目と組み合わせればかなり効果はあると思いますので、あくまで総量との見合いになることは気に留めて置いて下さい。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/11/30 14:39