NUMERIC型の列に関数を使用してデータを格納する際の考慮事項

1 はじめに
2 パラメータ
- 2.1 １．桁あふれを制御するパラメータ「AllowNumericOverflow」
- 2.2 ２．NUMERIC型の算出時の精度を指定するパラメータ「NumericSumExtraPrecisionDigits」
3 考慮点
- 3.1 １．性能に関する考慮
- 3.2 ２．算出時の精度に関する考慮
4 実行例
- 4.1 例１）桁あふれが格納されるケース
- 4.2 例２）桁あふれで格納されないケース
5 参考情報
6 検証バージョンについて
7 更新履歴

はじめに

NUMERIC型のデータをSUM、SUM_FLOAT、およびAVGを関数を使用して格納先の列の指定したサイズを超えた場合、桁あふれしたデータが格納されます。
例えば、NUMERIC型のデータをSUM関数を使用し合計した結果が123456の6桁の場合、NUMERIC(5)の列に桁あふれした123456が格納されます。
この動作は仕様でデフォルトの動作となっておりますが、Vertica8.0からパラメータを設定し動作を変更することができます。

NUMERIC型でSUM、SUM_FLOAT、AVG関数を使用していなければ、桁あふれのデータが格納されることはありません。

パラメータ

桁あふれ時の動作を変更するパラメータは下記の２つになります。

１．桁あふれを制御するパラメータ「AllowNumericOverflow」

パラメータ値	概要
1(TRUE)	デフォルトの値です。数値がオーバーフローして格納されることを許可します。
0(FALSE)	数値がオーバーフローして格納されることを許可しません。設定された精度を超えると、Verticaはオーバーフローエラーを生成します。

２．NUMERIC型の算出時の精度を指定するパラメータ「NumericSumExtraPrecisionDigits」

パラメータ値	概要
0～20の整数	結果が指定された精度を超えると、Verticaはオーバーフローエラーを生成します。
6	デフォルトの値です。

考慮点

桁あふれのデータを格納しない設定（AllowNumericOverflow=0)をした場合は以下の2点について考慮します。

１．性能に関する考慮

NUMERIC型は、18桁単位で領域を確保します。
桁あふれの判断するための算出時の精度は、列のNUMERICの桁数に「NumericSumExtraPrecisionDigits」の値を加算した桁数になります。
その桁数が19桁以上になる場合は、18桁に18桁を加えた36桁の桁数を確保する必要があるため性能に影響が出る可能性があります。
性能を考慮する場合は、可能な限り18桁以内になるようにパラメータを調整してください。

例えば、以下の例は桁あふれの判断をする精度は11桁で、18桁以内であるため性能に影響はありません。

しかし、以下の例のように桁あふれを判断する算出時の精度が21桁である場合、36桁の領域が必要となるため性能に影響が出る可能性があります。

２．算出時の精度に関する考慮

NUMERICデータ型のデータをSUM、SUM_FLOAT、およびAVG関数によって算出します。
算出時の精度は算出結果を格納する先の列サイズより1桁以上大きくなければ、桁あふれの判断ができないためデータがそのまま格納されてしまいます。
桁あふれのデータを格納されないようにするには、格納する先の列サイズより1桁以上大きくなるように「NumericSumExtraPrecisionDigits」の値を設定する必要があります。
以下の例が桁あふれが格納されるケースと格納されないケースです。

例１）桁あふれが格納されるケース
　　　桁あふれを判断するための算出時の精度と格納先より精度が同じ場合、桁あふれの判断ができないため、桁あふれのデータが格納されてしまいます。

例２）桁あふれが格納されないケース
　　　桁あふれを判断するための算出時の精度より格納先の精度が小さい場合
　　　桁あふれの判断ができるため、桁あふれのデータはエラーで格納されることはありません。

実行例

例１）桁あふれが格納されるケース

同じデータ型とサイズの2つのテーブルが存在します。
パラメータはデフォルトのままで、一方の列データをSUM関数で合計し、別のテーブルに挿入します。
SUM関数による合計値は、挿入先の列の精度を超えているが桁あふれの値が挿入されます。

[パラメータ]
AllowNumericOverflow = 1
NumericSumExtraPrecisionDigits = 6

・同じデータ型とサイズの２つのテーブルが存在します。

dbadmin=> \d tbl1;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key	
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | tbl1  | col1   | numeric(5,0) |    8 |         | f        | f           |
(1 row)

dbadmin=> \d tbl2;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | tbl2  | col1   | numeric(5,0) |    8 |         | f        | f           |
(1 row)

dbadmin=> \d tbl1;

List of Fields by Tables

--------+-------+--------+--------------+------+---------+----------+-------------+-------------

public | tbl1 | col1 | numeric(5,0) | 8 | | f | f |

(1 row)

dbadmin=> \d tbl2;

List of Fields by Tables

--------+-------+--------+--------------+------+---------+----------+-------------+-------------

public | tbl2 | col1 | numeric(5,0) | 8 | | f | f |

(1 row)

・tbl1(テーブル)には、2件のデータが格納されています。

dbadmin=> select * from tbl1;
 col1
-------
1
99999
(2 rows)

dbadmin=> select * from tbl1;

col1

-------

99999

(2 rows)

・tbl1.col1のデータをSUM関数で合計した結果をtbl2.col1に挿入します。

dbadmin=> insert into tbl2 select sum(col1) from tbl1;
 OUTPUT
--------
1
(1 row)

dbadmin=> insert into tbl2 select sum(col1) from tbl1;

OUTPUT

--------

(1 row)

エラーがなく挿入されました。

・tbl2を検索してデータを確認します。

dbadmin=> select * from tbl2;
  col1
--------
100000
(1 row)

dbadmin=> select * from tbl2;

col1

--------

100000

(1 row)

tbl2.col1がNUMERIC(5)であるのに、6桁のデータが挿入されました。

例２）桁あふれで格納されないケース

同じデータ型とサイズの2つのテーブルが存在します。
パラメータを調整し、一方の列データをSUM関数で合計し、別のテーブルに挿入します。
SUM関数による合計値は、挿入先の列の精度を超えているためエラーで挿入されません。

[パラメータ]
AllowNumericOverflow = 0
NumericSumExtraPrecisionDigits = 6

・同じデータ型とサイズの２つのテーブルが存在します。

dbadmin=> \d tbl1;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | tbl1  | col1   | numeric(5,0) |    8 |         | f        | f           |
(1 row)

dbadmin=> \d tbl2;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 public | tbl2  | col1   | numeric(5,0) |    8 |         | f        | f           |
(1 row)

dbadmin=> \d tbl1;

List of Fields by Tables

--------+-------+--------+--------------+------+---------+----------+-------------+-------------

public | tbl1 | col1 | numeric(5,0) | 8 | | f | f |

(1 row)

dbadmin=> \d tbl2;

List of Fields by Tables

--------+-------+--------+--------------+------+---------+----------+-------------+-------------

public | tbl2 | col1 | numeric(5,0) | 8 | | f | f |

(1 row)

・tbl1(テーブル)には、2件のデータが格納されています。

dbadmin=> select * from tbl1;
 col1
-------
1
99999
(2 rows)

dbadmin=> select * from tbl1;

col1

-------

99999

(2 rows)

・tbl1.col1のデータをSUM関数で合計した結果をtbl2.col1に挿入します。

dbadmin=> insert into tbl2 select sum(col1) from tbl1;
ERROR 5411:  Value exceeds range of type numeric(5,0)

1 2	dbadmin=> insert into tbl2 select sum(col1) from tbl1; ERROR 5411: Value exceeds range of type numeric(5,0)

桁あふれのデータを挿入しようとしたためエラーで挿入されませんでした。

参考情報

マニュアル「Numeric Data Type Overflow with SUM, SUM_FLOAT, and AVG」
https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/HowVerticaHandlesOverflowSUMNumeric.htm

検証バージョンについて

この記事の内容はVertica 10.1で確認しています。

更新履歴

2021/5/26　本記事を公開

投稿タグ: AllowNumericOverflow, NumericSumExtraPrecisionDigits, numeric, overflow

MENU

NUMERIC型の列に関数を使用してデータを格納する際の考慮事項

はじめに

パラメータ

１．桁あふれを制御するパラメータ「AllowNumericOverflow」

２．NUMERIC型の算出時の精度を指定するパラメータ「NumericSumExtraPrecisionDigits」

考慮点

１．性能に関する考慮

２．算出時の精度に関する考慮

実行例

例１）桁あふれが格納されるケース

例２）桁あふれで格納されないケース

参考情報

検証バージョンについて

更新履歴

カテゴリー

最近の投稿