by Hayato Matsuura.

数値文字列


数値文字列は、テキストの列に保存される数値のことです。 それ自体好ましくない方法なのですが、一貫して文字列として扱われて しまうと、自動的にインデックスが意味をなさなくなってしまいます。

SELECT ...
  FROM ...
 WHERE numeric_string = '42'

もちろんこの文は、インデックスNUMERIC_STRINGを 使えます。しかし、これを数字と比較してしまうと、データベースは この条件をアクセス述語と して使うことはできません。

SELECT ...
  FROM ...
 WHERE numeric_string = 42

引用符がないことに注意しましょう。データベースによっては (例、PostgreSQL)エラーを返しますが、多くのデータベースでは暗黙的な 型変換が行われるだけです。

SELECT ...
  FROM ...
 WHERE TO_NUMBER(numeric_string) = 42

これも前と同じ問題が発生します。関数の呼び出しがあるため、 NUMERIC_STRINGのインデックスは使われなくなって しまいます。解決策も前と同じく、テーブルの列を変換するのではなく、 検索語を変換すること、です。

SELECT ...
  FROM ...
 WHERE numeric_string = TO_CHAR(42)

データベースはなぜ自動的にそうしてしまわないのか疑問に 思いませんか?文字列を数値に変換するのは、常に同じ結果に なります。しかし、その逆は成り立ちません。文字列としてフォーマット された数値は、スペースや句読点、あるいは先頭にゼロを含んでいる 可能性があります。ある1つの値でも、以下のように複数の書き方が あります。

42
042
0042
00042
...

データベースは、NUMERIC_STRING列で使われている 数値のフォーマットが分からないので、必ず一意な結果が得られる 文字列から数値への変換を行うのです。

TO_CHAR関数は、数値に対して1つの文字列表現しか 返しません。リストの中で一番上にあるものにのみ一致するでしょう。 TO_CHARを使うと、文字列は一覧中の全てに一致するように なります。つまり、その2つのやり方に、パフォーマンス上の違いだけでなく 意味上の違いも生まれてしまうのです!

数値文字列を使うと、大抵問題を発生させることになります。 最も重要なのは、暗黙的な型変換によってパフォーマンス上の問題を引き起こす ことがあるのと、不正な数字のために変換エラーが起きるリスクを抱えて しまうことです。where句に関数を使っていない、 ごく一般的なクエリであっても、テーブル上に1つ不正な数値が保存 されているだけで変換エラーで処理が中断されてしまいます。

ヒント

数値を保存する時は、数値型を使いましょう。

逆の場合には、問題はないことも覚えておきましょう。

SELECT ...
  FROM ...
 WHERE numeric_number = '42'

データベースは、一貫して文字列から数値への変換を行います。 インデックスがある列に関数を適用してしまうことはありませんので、 通常のインデックスであれば正しく動作します。にもかかわらず、 間違った方法で手動で変換してしまわないようにしましょう。

SELECT ...
  FROM ...
 WHERE TO_CHAR(numeric_number) = '42'

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license