数値文字列は、テキストの列に保存される数値のことです。それ自体好ましくない方法なのですが、一貫して文字列として扱われて しまうと、自動的にインデックスが意味をなさなくなってしまいます。
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列で使われている
数値のフォーマットが分からないので、必ず一意な結果が得られる文字列から数値への変換を行うのです。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
TO_CHAR関数は、数値に対して1つの文字列表現しか
返しません。リストの中で一番上にあるものにのみ一致するでしょう。
TO_NUMBERを使うと、文字列は一覧中の全てに一致するように
なります。つまり、その2つのやり方に、パフォーマンス上の違いだけでなく意味上の違いも生まれてしまうのです！
数値文字列を使うと、大抵問題を発生させることになります。最も重要なのは、暗黙的な型変換によってパフォーマンス上の問題を引き起こす
ことがあるのと、不正な数字のために変換エラーが起きるリスクを抱えてしまうことです。where句に関数を使っていない、
ごく一般的なクエリであっても、テーブル上に1つ不正な数値が保存されているだけで変換エラーで処理が中断されてしまいます。
ヒント
数値を保存する時は、数値型を使いましょう。
逆の場合には、問題はないことも覚えておきましょう。
SELECT ...
FROM ...
WHERE numeric_number = '42'データベースは、一貫して文字列から数値への変換を行います。インデックスがある列に関数を適用してしまうことはありませんので、 通常のインデックスであれば正しく動作します。にもかかわらず、間違った方法で手動で変換してしまわないようにしましょう。
SELECT ...
FROM ...
WHERE TO_CHAR(numeric_number) = '42'
