AS/400の数値型に対して、SQLで条件指定する場合の注意点

※個人の備忘録からの投稿で、一般化したスクリーンショットを用意できていません。すみません。

■まず結論から

結論から言えば、安全策として、

○　select * from AAA.BBB where slipno = '1705';

のように数値であっても文字型として指定することを心掛けるとトラブルを回避できます。

■解説

●問題が起きる事例

BBBは物理ファイルで『slipno』以外がキー、
BBBZは『slipno』がキーとなっている論理ファイルです。
『slipno』は数値型のnumericです。

×　select * from AAA.BBB where slipno = 1705;
○　select * from AAA.BBBZ where slipno = 1705;
○　select * from AAA.BBB where slipno = '1705';
○　select * from AAA.BBBZ where slipno = '1705';

データが1000万件以上ある環境下でしたが、
○は0.1秒以下
×は180秒以上
の処理時間が掛かりました。

●問題が起きる理由

• AS400内RPGで使用していた数値型はNUMERIC、SQLクエリ上の数値型はINTEGERで、データ型が異なる

• 基本的には、照合ベースとしては、NUMERICよりINTEGER側が精度が高いということで優先される
  　⇒INTEGERが優先される
  　⇒全行でキャストが行われ、当然索引も利用されない

• 論理ファイルを直接指定した場合は、(キーに関しては)照合の際の型が、論理ファイル側が優先される　(索引を利用するという明確な意思があるため)
  　⇒(論理ファイル側の型である)NUMERICが優先される

●検証

1. ○　select * from AAA.BBB where slipno = cast(1705 as numeric);
2. ×　select * from AAA.BBB where slipno = cast(1705 as integer);
3. ×　select * from AAA.BBB where slipno = 1705;
4. ○　select * from AAA.BBB where slipno = '1705';
5. ○　select * from AAA.BBBZ where slipno = cast(1705 as numeric);
6. ○　select * from AAA.BBBZ where slipno = cast(1705 as integer);
7. ○　select * from AAA.BBBZ where slipno = 1705;
8. ○　select * from AAA.BBBZ where slipno = '1705';

※これらを実行し、利用された索引名をVisualExplainで確認しました。
※社内環境での検証のためスクショ載せられずすみません。

1. ○　select * from AAA.BBB where slipno = cast(1705 as numeric);
   　明示的にNUMERICにキャストした場合は、型が同一のため、高速になる。
   　索引も自動的にBBBZが使用される。

2. ○　select * from AAA.BBB where slipno = '1705';
   　文字列型の場合、NUMERICが優先されるため、’1705’　は内部で　cast(1705 as numeric)　され、
   　同様に索引はBBBZが使用され、高速に処理される。

3. ×　select * from AAA.BBB where slipno = 1705;
   　INTEGERのほうが優先度が高く、
   　NUMERIC側(slipno)をキャストするため、索引(インデックス)が利用されず、非常に低速となる。

4. ○　select * from AAA.BBBZ where slipno = 1705;
   　ただし、例外的に、明示的にBBBZを指定した場合は、
   　索引の型が優先され、1705　は内部で　cast(1705 as numeric)　されるようで
   　索引が利用され、高速に処理される。

5. ○　select * from AAA.BBBY where slipno = '1705';
   　なお、素晴らしいことに、適切でない論理ファイル名(BBBY)を指定した場合であっても、
   　オプティマイザが適切な索引を利用しようとして、自動的にBBBZを利用して処理されている。(これは素晴らしい！)
   　当然、高速に処理される。

●まとめ

まとめると、正しい論理ファイルを指定した場合は、SQL文の中で数値指定(つまりINTEGER)でも問題ないが、論理ファイル名を間違えたり、単に物理ファイル名の指定では、索引が適用されず、テーブルスキャンかつキャスト処理で、非常に重たい処理となってしまう。
SQL文の中で文字列として指定した場合は、NUMERIC型が優先されるため、文字列がNUMERIC型に一度だけキャストされる。さらにこの場合は、どの論理ファイル・物理ファイルを指定していても、適切な索引が自動で判定されて、利用される。
一応、cast(1705 as numeric)　のようにキャストしても良いが、事前にどの数値型か調べる必要があり、書き方として面倒でもあるので、文字列型で指定する方法は、非常に便宜的といえる。

付記

Visual Explainでは使用された索引の名前として論理ファイル名が表示されます。