Home » Oracle » [Oracle] OracleのNUMBER型の謎

[Oracle] OracleのNUMBER型の謎

2020年6月22日月曜日

Oracle

PLS_INTEGER

OracleのPL/SQLにはテーブルには使えない数値型が存在する。
これはNUMBER型のような10進型ではなく、Cのintのような内部2進の32ビット整数型なので高速に動作する。
declare
  i_number number := 0;
  i_pls_integer pls_integer := 0;
  start_time number;
  elapsed_time number;
begin
  start_time := dbms_utility.get_time();
  for i in 1..10000000 loop
    i_number := i_number + 1;
  end loop;
  elapsed_time := dbms_utility.get_time() - start_time;
  dbms_output.put_line(elapsed_time * 10);

  start_time := dbms_utility.get_time();
  for i in 1..10000000 loop
    i_pls_integer := i_pls_integer + 1;
  end loop;
  elapsed_time := dbms_utility.get_time() - start_time;
  dbms_output.put_line(elapsed_time * 10);
end;
/
-----
210
70

PL/SQL内で一時的に使う数値で、PLS_INTEGERの範囲に収まるのであれば、NUMBERではなくPLS_INTEGERの使用を検討するといいかもしれない。

NUMBER? INTEGER?

PLS_INTEGERと同じように、NUMBERとINTEGERの動作速度を比較してみよう。
declare
  i_integer integer := 0;
  start_time number;
  elapsed_time number;
begin
  start_time := dbms_utility.get_time();
  for i in 1..10000000 loop
    i_integer := i_integer + 1;
  end loop;
  elapsed_time := dbms_utility.get_time() - start_time;
  dbms_output.put_line(elapsed_time * 10);
end;
/
-----
410

INTEGERは標準SQLの整数型をサポートするためのエイリアスで実際には小数部が0桁のNUMBERだ。

https://docs.oracle.com/cd/F19136_01/sqlrf/Data-Types.html#GUID-0BC16006-32F1-42B1-B45E-F27A494963FF

NUMBERよりINTEGERが遅くなっている。なんで、こんな違いが出るんだろう。
INTEGERではなく、明示的にNUMBER(38, 0)でやってみよう。
declare
  i_number38 number(38) := 0;
  start_time number;
  elapsed_time number;
begin
  start_time := dbms_utility.get_time();
  for i in 1..10000000 loop
    i_number38 := i_number38 + 1;
  end loop;
  elapsed_time := dbms_utility.get_time() - start_time;
  dbms_output.put_line(elapsed_time * 10);
end;
/
-----
410

うーん、INTEGERの場合と同じだね。
どうも、桁数を指定しないNUMBERは特別みたい。

NUMBER(p,s)を確認

ちょっとNUMBER(p,s)の挙動を確認。
declare
  n number(4,2);
begin
  n := 12.344;
  dbms_output.put_line(n);
  n := 12.345;
  dbms_output.put_line(n);
  n := 99.994;
  dbms_output.put_line(n);
  n := 99.995;
  dbms_output.put_line(n);
end;
/

12.34
12.35
99.99
ORA-06502: PL/SQL: 数値または値のエラー: 数値の精度が大きすぎます。が発生しました

マニュアルを見ると、

https://docs.oracle.com/cd/F19136_01/sqlrf/Data-Types.html#GUID-75209AF6-476D-4C44-A5DC-5FA70D701B78
「値が精度の有効範囲を超えると、Oracleはエラーを戻します。値が位取りの有効範囲を超えると、Oracleはその値を丸めます。」

桁数をチェックしているのではなく、数値がスケール指定した固定小数点数の範囲に収まるかどうかのチェックになっている。

素のNUMBERは？

では、桁数とスケールを明示しない素のNUMBERでやってみよう。
declare
  n_max number := 9.999999999999999999999999999999999999999E+125;
  n_round number := n_max + 4E+85;
  n_over number := n_max + 5E+85;
  d_max varchar2(256);
  d_round varchar2(256);
  d_over varchar2(256);
begin
  select dump(n_max, 16), dump(n_round, 16), dump(n_over, 16)
    into d_max, d_round, d_over from dual;
  dbms_output.put_line(n_max);
  dbms_output.put_line(d_max);
  dbms_output.put_line(n_round);
  dbms_output.put_line(d_round);
  dbms_output.put_line(n_over);
  dbms_output.put_line(d_over);
end;
/

9.999999999999999999999999999999999999999000000000000000000000000000000000000000000000000000000E+125
Typ=2 Len=21: ff,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
9.999999999999999999999999999999999999999000000000000000000000000000000000000000000000000000000E+125
Typ=2 Len=21: ff,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64,64
~
Typ=2 Len=2: ff,65

前出のマニュアルを再度見ると、

https://docs.oracle.com/cd/F19136_01/sqlrf/Data-Types.html#GUID-75209AF6-476D-4C44-A5DC-5FA70D701B78
「Oracleは、100進数で20桁までの精度で数値の移植性を保証します。」

とあるので、1バイトで10進2桁を保持して、20バイトで40桁まで保持できることになる。

つまり、NUMBER(p,s)形式の型は代入時に毎回範囲チェックをしている。
素のNUMBERは範囲チェックをせずにあふれた分は切り捨ててしまう。
さらにオーバーフローしてもエラーにならずにオーバーフローを表す値になってしまう。
マニュアルに記載されている「精度および位取りを指定しない場合、最大の範囲および精度をOracleの数値に指定したことになります。」の意味がやっと分かった。

NUMBERとINTEGERの動作速度の違いはここから来ているんだね。

OracleのNUMBER型について、やっと納得できたような気がする。
時刻:
ラベル: