質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Oracle

Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

4回答

8219閲覧

【SQL】REGEXP_LIKEの性能について

jimxx

総合スコア8

Oracle

Oracleは、米オラクルが取り扱うリレーショナルデータベース管理システムです。メインフレームからPCまで、多様なプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2017/11/30 05:58

編集2017/11/30 14:53

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の方が可読性が高いので採用したいと思っています。

もし他に良い条件式がある場合はご教示願います。
ご回答よろしくお願いいたします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答4

0

ベストアンサー

文字列の列に使われるような索引は、通常Bツリー形式ですが、これは仕組み上先頭一致、あるいは連続した範囲の検索にしか使えません。

なぜBTreeがIndexに使われているのか

「最後1文字だけ」のような関数インデックスがあるのなら格別、通常のBツリーインデックスのみの環境では、テーブルスキャン以外の手段はありません。

投稿2017/11/30 07:00

maisumakun

総合スコア145121

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

jimxx

2017/11/30 14:39

ご回答ありがとうございます! やはりREGEXP_LIKEによる後方一致はテーブルスキャンになるのですね。リンクもありがとうございます。索引についてもっと学習していきたいと思います。
guest

0

他の人も書いているようにファンクションインデックスを使う方法がありますが、「1つの列には1つの情報しか持たせない」というテーブル設計の定石があります。
今さらテーブル設計を変更できないという事情があるかもしれないですが、列の最後の1桁を別の列として設計するべきではなかったでしょうか?

投稿2017/12/01 01:41

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

jimxx

2017/12/01 12:22

ご回答ありがとうございます。 今回の抽出条件に対してテーブルの設計やIDの採番方法について改善の余地があることは理解しておりますが、システム構築時 からは携わってきていないので、設計思想についてはまだ理解が及んでおりません。 テーブル設計の定石など、とても勉強になりました。ありがとうございました。
guest

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

sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

jimxx

2017/11/30 14:52

ご回答ありがとうございます! 記載ミスのご指摘ありがとうございます。 SUBSTRで索引走査がしたいのではなく、可読性・保守性と性能のバランスが良いSQLを作りたく試行錯誤しております。 ご教示いただいた条件式だと記載がかなり長くなりますが、どの程度性能が上がるのでしょうか。
sazi

2017/11/30 23:09 編集

どの程度性能が上がるかは、前提として抽出対象のカーディナリティが高くインデックスの適用があることによります。 カーディナリティが高いなら、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で分布が均等なら、全表検索後のフィルター以上にはならないので改善はあまり見込めないと思います。
jimxx

2017/12/01 12:17

ありがとうございます。 とても勉強になりました。 やはり今回は最初の方法を採用いたします。
sazi

2017/12/01 12:27

母数が長大なら10分の1でも他の項目と組み合わせればかなり効果はあると思いますので、あくまで総量との見合いになることは気に留めて置いて下さい。
guest

0

どうしてもその条件で全表スキャンを避けたいのであれば、ファンクション・インデックスを作成する手がありますよ。

もしくは、ID下一桁が1かどうかを表すフラグを作ってそこにインデックスを貼るとか。

ただ、IDが連番なら1でないレコードは9割になるので、全表スキャンの方が早いですよ。

投稿2017/11/30 07:12

ka_ei

総合スコア207

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

jimxx

2017/11/30 14:42

ご回答ありがとうございます。 >ただ、IDが連番なら1でないレコードは9割になるので、全表スキャンの方が早いですよ。 納得しました!!こういう思考できるよう経験を積んでいきたいと思います。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問