五月 21, 2023
在 Oracle 中，NUMBER 数据类型可用来存储固定和浮点数字。数据库可以存储几乎任何规模的数字。此数据保证在运行 Oracle 数据库的不同操作系统之间可移植。大多数情况下，当您必须存储数值数据时，推荐使用 NUMBER 数据类型。
Oracle 中的 NUMBER 类型
按 NUMBER(p, s) 的形式定义定点数，p 和 s 有以下特征：
-
精度
精度指定数字的总长度。如果不指定精度，则列按应用程序提供的数据存储值，不作任何舍入。
-
小数位数
小数位数指从十进制的小数点到最小有效数字的位数。正小数位数从小数点向右计数直至最小有效位。负小数位数从小数点向左计数直至（但不包括）最小有效数字。如果您指定了精度但没有指定小数位数，如NUMBER(6)，那么小数位数为 0。
许多工具经常在 PostgreSQL 中将此数据类型转换为 NUMERIC。虽然将 Oracle NUMBER 转换为 PostgreSQL NUMERIC 看起来很容易，但它的性能并不理想，因为与整数类型相比，NUMERIC 上的计算非常慢。除非具有无法存储在 BIGINT 中的无比例的较大值，否则无需选择列数据类型 NUMERIC。只要列数据没有浮点值，就不需要将列定义为 DOUBLE PRECISION 数据类型。这些列可以根据存储的值为 INT 或 BIGINT。因此，值得进行前期分析，以确定在迁移到 PostgreSQL 时是否应将 Oracle NUMBER 数据类型转换为 INT，BIGINT，DOUBLE PRECISION 或 NUMBER。
在这篇文章中，我们介绍了两种分析方法来定义 PostgreSQL 中的目标数据类型列，具体取决于 NUMBER 数据类型在 Oracle 数据库中的定义方式以及列数据中存储的值。在我们建议 PostgreSQL 中的哪种数据类型适合 Oracle NUMBER 之前，重要的是要了解这些 INT，BIGINT，DOUBLE PRECISION 和 NUMERIC 之间的主要区别。
比较 PostgreSQL 中的数值类型
INT 和 BIGINT 与 NUMERIC 之间的重要区别之一是存储格式。INT 和 BIGINT 有固定长度：INT 有 4 个字节，BIGINT 有 8 个字节。但是，NUMERIC 类型长度可变，并根据需要存储 0–255 个字节。因此，NUMERIC 需要更多的空间来存储数据。让我们看一下以下示例代码（我们使用 PostgreSQL 12），它显示了具有整数列和 NUMERIC 的表的大小差异：
postgres=# SELECT pg_column_size('999999999'::NUMERIC) AS "NUMERIC length",
pg_column_size('999999999'::INT) AS "INT length";
NUMERIC length | INT length
----------------+------------
12 | 4
(1 row)
postgres=# CREATE TABLE test_int_length(id INT, name VARCHAR);
CREATE TABLE
postgres=# CREATE TABLE test_numeric_length(id NUMERIC, name VARCHAR);
CREATE TABLE
postgres=# INSERT INTO test_int_length VALUES (generate_series(100000,1000000));
INSERT 0 900001
postgres=# INSERT INTO test_numeric_length VALUES (generate_series(100000,1000000));
INSERT 0 900001
postgres=# SELECT sum(pg_column_size(id))/1024 as "INT col size",
pg_size_pretty(pg_total_relation_size('test_int_length')) as "Size of table"
FROM test_int_length;
INT col size | Size of table
--------------+----------------
3515 | 31 MB
(1 row)
Time: 104.273 ms
postgres=# SELECT sum(pg_column_size(id))/1024 as "NUMERIC col size",
pg_size_pretty(pg_total_relation_size('test_numeric_length')) as "Size of table"
FROM test_numeric_length;
NUMERIC col size | Size of table
------------------+----------------
6152 | 31 MB
(1 row)
从前面的输出中，INT 占用 3，515 字节，对于同一组值，NUMERIC 占用 6，152 字节。但是，您会看到相同的表大小，因为 PostgreSQL 的设计使其自身的内部自然对齐为 8 个字节，这意味着在某些情况下，必须用空字节填充不同大小的连续固定长度列。我们可以通过以下代码看到这一点：
SELECT pg_column_size(row()) AS empty,
pg_column_size(row(0::SMALLINT)) AS byte2,
pg_column_size(row(0::BIGINT)) AS byte8,
pg_column_size(row(0::SMALLINT, 0::BIGINT)) AS byte16;
empty | byte2 | byte8 | byte16
-------+-------+-------+--------
24 | 26 | 32 | 40
从前面的输出中可以清楚地看出，一个空的 PostgreSQL 行需要 24 个字节的各种标头元素，一个 SMALLINT 是 2 个字节，一个 BIGINT 是 8 个字节。但是，组合 SMALLINT 和 BIGINT 需要 16 个字节。这是因为 PostgreSQL 正在填充较小的列以匹配下一列的大小以进行对齐。大小变为 2 + 8 = 10，而不是 8 + 8 = 16。
整数和浮点值表示及其算术运算的实现方式不同。通常，浮点运算比整数算术更复杂。与整数类型相比，它需要更多的时间来计算数值或浮点值。
PostgreSQL BIGINT 和 INT 数据类型存储自己的值范围。BIGINT 范围（8 字节）是-9223372036854775808到9223372036854775807：
postgres=# select 9223372036854775807::bigint;
int8
---------------------
9223372036854775807
(1 row)
postgres=# select 9223372036854775808::bigint;
ERROR: bigint out of range
INT 范围（4 个字节）是-2147483648到2147483647：
postgres=# select 2147483647::int;
int4
------------
2147483647
(1 row)
postgres=# select 2147483648::int;
ERROR: integer out of range
有关这些数据类型的信息，请参阅数值类型。
由于 NUMBER 数据类型在 Oracle 中具有精度和小数位数，因此在 PostgreSQL 中它可以是 DOUBLE PRECISION 或 NUMERIC 。双精度和数字之间的主要区别之一是存储格式和支持的精度或小数位数的总长度。双精度固定为 8 字节的存储和 15 位十进制数字的精度。
与 NUMERIC 数据类型相比，DOUBLE PRECISION 提供了类似的好处，正如 PostgreSQL 中为 BIGINT 和 INT 解释的那样。
使用 DOUBLE PRECISION，因为它的限制是 15 位小数，对于任何具有更高精度或小数的数据，我们可能会在从 Oracle 迁移数据期间受到数据截断的影响。请参阅以下代码：
postgres=> select 123456789.10111213::double precision as Scale_Truncated,
123456789101112.13::double precision as Whole_Scale_Truncated;
scale_truncated | whole_scale_truncated
------------------+-----------------------
123456789.101112 | 123456789101112
当 NUMBER 数据类型具有十进制信息时，在 PostgreSQL 中考虑数据类型时，我们应该检查最大精度和最大小数位数，并相应地决定目标数据类型是双精度还是数字。
如果您计划存储需要特定精度或算术精度的值，则 DOUBLE PRECISION 数据类型可能是满足您需求的正确选择。例如，如果您尝试存储 2/3 的结果，则在使用 DOUBLE PRECISION 时达到第 15 位数字时会有一些舍入。它不仅用于根据精度限制对值进行舍入，还用于算术精度。如果考虑以下示例，则双精度会给出错误的答案。
postgres=# select 0.1::double precision + 0.2 as value;
value
---------------------
0.30000000000000004
(1 row)
postgres=# select 0.1::numeric + 0.2 as value;
value
-------
0.3
(1 row)
从 PostgreSQL 12 开始，通过使用新算法输出实精度值和双精度值，性能得到了提高。在以前的版本中（早于 PostgreSQL 12），显示的浮点值默认舍入为 6（实数）或 15（双精度）数字，由参数extra_float_digits的值调整。现在，只要extra_float_digits大于零（从 PostgreSQL 12 及更高版本开始默认如此），只会输出保留确切二进制值所需的最小位数。当设置extra_float_digits为零或更小时，行为与之前相同。
postgres=# set extra_float_digits to 0;
SET
postgres=# select 0.1::double precision + 0.2 as value;
value
-------
0.3
(1 row)
Oracle NUMBER 和 PostgreSQL NUMERIC
在 Oracle 中，NUMBER 数据类型定义为 NUMBER(precision, scale)，而在 PostgreSQL 中，NUMERIC 定义为 NUMERIC(precision, scale)，精度和小数位数定义如下：
- 精度 – 整数中有效数字的总数，即小数点两侧的位数
- 小数位数 – 小数部分的小数位数计数，位于小数点右侧
数据类型转换前的分析
数据类型更改可能会有一些副作用，我们将在本文中讨论。在转换数据类型之前，您需要与服务或应用程序团队核实他们是否具有以下条件：
- 需要类型转换的应用程序 SQL 中这些数据类型（INT 和 BIGINT）的任何依赖关系
- 这些数据类型的存储过程或触发器中所需的任何更改
- 休眠生成的代码中的任何依赖项
- 如果这些数据类型更改对未来的数据增长有任何影响
- 将来这些列存储任何小数值的任何计划
如果没有对应用程序代码和数据库代码对象进行适当的分析和信息，则不建议更改数据类型。如果这样做，应用程序查询可能会开始显示性能问题，因为它们需要在内部从 NUMERIC 转换为 INT 或 BIGINT。如果代码对象（如过程或触发器）具有这些数据类型的依赖项，则需要对这些对象进行更改以避免性能问题。未来的数据增长也可能影响这些转化。但是，如果根据当前数据将其转换为 INT，则将来可能会超出 INT 值。请确保在迁移完成后没有任何存储小数值的计划。如果这是要求，则需要选择与 INT 或 BIGINT 不同的数据类型。
在这篇文章中，我们研究了两种不同的方法来分析信息以推荐 INT、BIGINT、DOUBLE PRECISION 或 NUMERIC：
- 基于元数据的数据类型转换
- 实际基于数据的数据类型转换
分析方法：基于元数据的数据类型转换
通过查看具有 NUMBER 或 INTEGER 数据类型列的 Oracle 表的元数据信息，我们可以提出目标数据类型建议。
转换为PostgreSQL INT或BIGINT
您可以将数据类型为 NUMBER 或 INTEGER 的列的 Oracle 表隐藏到 PostgreSQL INT 或 BIGINT，前提是它们满足以下条件：
- 在 Oracle 中，数据类型为 NUMERIC 或 INTEGER
- DATA_PRECISION不是 NULL（不是可变长度的数字）
- DATA_SCALE为 0（整数而不是浮点值）
- MAX_LENGTH定义为 <=18 （DATA_PRECISION <=18）
- 如果DATA_PRECISION< 10，请使用 INT。如果DATA_PRECISION是 10-18，请使用 BIGINT。
您可以在 Oracle 上使用以下查询来查找可在 PostgreSQL 中转换为 BIGINT 或 INT 的 NUMERIC 或 INTEGER 数据类型的候选列：
SELECT
OWNER
, TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
, DATA_PRECISION
, DATA_SCALE
FROM
dba_tab_columns
WHERE
OWNER in ('owner_name')
and (DATA_TYPE='NUMBER' or DATA_TYPE='INTEGER')
and DATA_PRECISION is NOT NULL
and (DATA_SCALE=0 or DATA_SCALE is NULL)
and DATA_PRECISION <= 18;
以下代码是上述查询的示例输出，它显示了特定用户的 NUMBER 数据类型的精度和小数位数：
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ---------------- ---------------- ---------- -------------- ----------
DEMO COUNTRIES REGION_ID NUMBER 10 0
DEMO CUSTOMER CUSTOMER_ID NUMBER 15 0
DEMO DEPARTMENTS DEPARTMENT_ID NUMBER 4 0
DEMO EMPLOYEES EMPLOYEE_ID NUMBER 6 0
DEMO EMPLOYEES MANAGER_ID NUMBER 6 0
DEMO EMPLOYEES DEPARTMENT_ID NUMBER 4 0
DEMO ORDERS ORDER_ID NUMBER 19 0
DEMO ORDERS VALUE NUMBER 14 0
DEMO PERSON PERSON_ID NUMBER 5 0
输出显示四列（COUNTRIES表的REGION_ID列、CUSTOMER表的CUSTOMER_ID列、和ORDERS表的ORDER_ID和VALUE列），精度在 10-18 之间，可以在 PostgreSQL 中转换为 BIGINT。其余列可以转换为 INT。
转换为PostgreSQL双精度或数字
如果符合以下条件，则可以将数据类型为 NUMBER 或 INTEGER 的列的 Oracle 表转换为 PostgreSQL 双精度或 NUMERIC ：
- 数据类型在 Oracle 中为 NUMERIC 或 NUMBER。
- DATA_PRECISION不是空的
- DATA_SCALE> 0（浮点值）
如果 DATA_PRECISION + DATA_SCALE <= 15，请选择“双精度”。如果DATA_PRECISION + DATA_SCALE > 15，请选择“数字”。
您可以在 Oracle 上使用以下查询来查找 NUMERIC 或 INTEGER 数据类型的候选列，这些列可以在 PostgreSQL 中转换为 DOUBLE PRECISION 或 NUMERIC ：
SELECT
OWNER
, TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
, DATA_PRECISION
, DATA_SCALE
FROM
dba_tab_columns
WHERE
OWNER in ('owner_name')
and (DATA_TYPE='NUMBER' or DATA_TYPE='INTEGER')
and DATA_PRECISION is NOT NULL
and DATA_SCALE >0;
以下是查询的示例输出：
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ---------------- ---------------- ---------- -------------- ----------
DEMO COORDINATES LONGI NUMBER 10 10
DEMO COORDINATES LATTI NUMBER 10 10
DEMO EMPLOYEES COMMISSION_PCT NUMBER 2 2
输出包含EMPLOYEES表中的COMMISSION_PCT列，该列可以转换为双精度。输出还包含两列（LONGI和LATTI），可以是 NUMERIC 的，因为它们的（精度 + 小数位数）> 15。
更改所有候选列的数据类型可能不会产生相同的影响或性能提升。作为键或索引一部分的大多数列都显示在联接条件或查找中，因此更改这些列的数据类型可能会产生很大影响。以下是在 PostgreSQL 中按优先级顺序转换列数据类型的准则：
- 考虑更改属于键的列（主列、唯一列或引用列）的数据类型
- 考虑更改属于索引的列的数据类型
- 考虑更改所有候选列的数据类型
分析方法：实际基于数据的数据类型转换
在 Oracle 中，NUMBER 数据类型通常不带小数位数，这些列仅用于存储整数值。但由于未定义比例，因此如果这些列仅存储整数值，则不会显示元数据。在这种情况下，我们需要对这些列执行全表扫描，以确定这些列是否可以在 PostgreSQL 中转换为 BIGINT 或 INT。
NUMERIC 列的定义DATA_PRECISION可能大于 18，但所有列值都非常适合 PostgreSQL 的 BIGINT 或 INT 范围。
可以在 Oracle 数据库中使用以下 SQL 代码来查找实际的DATA_PRECISION和DATA_SCALE是否正在使用中。此代码执行全表扫描，因此您需要对某些表批量执行，如果可能，请在活动备用数据库 （ADG） 的低高峰时段运行它：
select /*+ PARALLEL(tab 4) */
max(length(trunc(num_col))) MAX_DATA_PRECISION,
min(length(trunc(num_col))) MIN_DATA_PRECISION,
max(length(num_col - trunc(num_col)) -1 ) MAX_DATA_SCALE
from <<your-table-name>> tab;
根据上述代码的结果，使用以下转换规则：
- 如果MAX_DATA_PRECISION < 10 且 MAX_DATA_SCALE = 0，则转换为 INT
- 如果DATA_PRECISION为 10–18 且 MAX_DATA_SCALE = 0，则转换为 BIGINT
- 如果MAX_DATA_SCALE > 0 且 MAX_DATA_PRECISION + MAX_DATA_SCALE <= 15，则转换为双精度
- 如果MAX_DATA_SCALE > 0 且 MAX_DATA_PRECISION + MAX_DATA_SCALE > 15，则转换为数字
让我们看下面的例子。在 Oracle 中，创建定义了四列并插入了几行的表number_col_test：
SQL> CREATE TABLE DEMO.number_col_test (
col_canbe_int NUMBER(8,2),
col_canbe_bigint NUMBER(19,2),
col_canbe_doubleprecision NUMBER(15,2),
col_should_be_number NUMBER(20,10)
);
Table created.
SQL> INSERT INTO demo.number_col_test VALUES (1234, 12345678900, 12345.12, 123456.123456);
1 row created.
SQL> INSERT INTO demo.number_col_test VALUES (567890, 1234567890012345, 12345678.12, 1234567890.1234567890);
1 row created.
SQL> set numwidth 25
SQL> select * from demo.number_col_test;
COL_CANBE_INT COL_CANBE_BIGINT COL_CANBE_DOUBLEPRECISION COL_SHOULD_BE_NUMBER
-------------- -------------- ------------------------- --------------------
1234 12345678900 12345.12 123456.123456
567890 1234567890012345 12345678.12 1234567890.123456789
接下来，我们运行一个查询来查看数据以查找实际的精度和小数位数。在以下示例中，can_be_int的精度为 6，小数位数为 0，即使它定义为 NUMBER(8, 4)。因此，我们可以在PostgreSQL中将此列定义为INT。
SQL> select /*+ PARALLEL(tab 4) */
max(length(trunc(COL_CANBE_INT))) MAX_DATA_PRECISION,
min(length(trunc(COL_CANBE_INT))) MIN_DATA_PRECISION,
max(length(COL_CANBE_INT - trunc(COL_CANBE_INT)) -1 ) MAX_DATA_SCALE
from demo.number_col_test;
MAX_DATA_PRECISION MIN_DATA_PRECISION MAX_DATA_SCALE
------------------------- ------------------------- -------------------------
6 4 0
在以下输出中，can_be_bigint的精度为 16，小数位数为 0，即使它定义为 NUMBER（19，2）。因此，我们可以在PostgreSQL中将此列定义为BIGINT。
SQL> select /*+ PARALLEL(tab 4) */
max(length(trunc(COL_CANBE_BIGINT))) MAX_DATA_PRECISION,
min(length(trunc(COL_CANBE_BIGINT))) MIN_DATA_PRECISION,
max(length(COL_CANBE_BIGINT - trunc(COL_CANBE_BIGINT)) -1 ) MAX_DATA_SCALE
from demo.number_col_test;
MAX_DATA_PRECISION MIN_DATA_PRECISION MAX_DATA_SCALE
------------------------- ------------------------- -------------------------
16 11 0
在以下输出中，can_be_doubleprecision的精度为 8，小数位数为 2，即使它定义为 NUMBER(15, 2)。我们可以在PostgreSQL中将此列定义为 DOUBLE PRECISION。
SQL> select /*+ PARALLEL(tab 4) */
max(length(trunc(COL_CANBE_DOUBLEPRECISION))) MAX_DATA_PRECISION,
min(length(trunc(COL_CANBE_DOUBLEPRECISION))) MIN_DATA_PRECISION,
max(length(COL_CANBE_DOUBLEPRECISION - trunc(COL_CANBE_DOUBLEPRECISION)) -1 ) MAX_DATA_SCALE
from demo.number_col_test;
MAX_DATA_PRECISION MIN_DATA_PRECISION MAX_DATA_SCALE
------------------------- ------------------------- -------------------------
8 5 2
在以下输出中，should_be_numeric的精度为 10，小数位数为 9，即使它定义为 NUMBER(20, 10)。我们可以在 PostgreSQL 中将此列定义为 NUMBER。
SQL> select /*+ PARALLEL(tab 4) */
max(length(trunc(COL_SHOULD_BE_NUMBER))) MAX_DATA_PRECISION,
min(length(trunc(COL_SHOULD_BE_NUMBER))) MIN_DATA_PRECISION,
max(length(COL_SHOULD_BE_NUMBER - trunc(COL_SHOULD_BE_NUMBER)) -1 ) MAX_DATA_SCALE
from demo.number_col_test;
MAX_DATA_PRECISION MIN_DATA_PRECISION MAX_DATA_SCALE
------------------------- ------------------------- -------------------------
10 6 9
总结
将 NUMBER 数据类型从 Oracle 转换为 PostgreSQL 总是很棘手。不建议在没有对源数据进行正确分析的情况下，在 PostgreSQL 中将所有 NUMBER 数据类型列转换为 NUMERIC 或 DOUBLE PRECISION。拥有适当的数据类型有助于提高性能。它通过预先花费时间来确定应用程序性能的正确数据类型来支付长期红利。从 Oracle 系统表中，您可以获得用户所有表的所有列的精度和小数位数。使用此元数据信息，您可以在 PostgreSQL 中选择目标数据类型为 INT、BIGINT、DOUBLE PRECISION 或 NUMERIC。
但是，您不能总是依赖此信息。尽管此元数据将某些列的小数位数显示为 >0，但实际数据可能没有浮点值。查看每个表的 NUMBER 列（小数位数为 >0）中的实际数据，并确定目标列的数据类型。
| 精度(m) | 刻度(n) | Oracle | PostgreSQL |
|---|---|---|---|
| <= 9 | 0 | NUMBER(m,n) | INT |
| 9 < m <=18 | 0 | NUMBER(m,n) | BIGINT |
| m+n <= 15 | n>0 | NUMBER(m,n) | DOUBLE PRECISION |
| m+n > 15 | n>0 | NUMBER(m,n) | NUMERIC |