How to Find the Internal Physical Length of a Number Datatype? (文档 ID 143256.1)

最新推荐文章于 2024-06-12 08:00:00 发布

转载最新推荐文章于 2024-06-12 08:00:00 发布 · 281 阅读

Oracle DB 专栏收录该内容

772 篇文章

订阅专栏

本文介绍了Oracle数据库中数值类型(NUMBER)的内部存储方式及其长度计算公式。Oracle使用变长格式存储数值，采用科学计数法表示，包括用于存储指数的一个字节和最多20个字节来存储尾数部分。文章还提供了根据数值精度计算列字节大小的公式。

How to Find the Internal Physical Length of a Number Datatype? (文档 ID 143256.1)

Oracle stores numeric data in variable-length format. Each value is stored in
scientific notation, with one byte used to store the exponent and up to 20
bytes to store the mantissa. The resulting value is limited to 38 digits of
precision. Oracle does not store leading and trailing zeros. For example, the
number 412 is stored in a format similar to 4.12 x 102, with one byte used to
store the exponent (2) and two bytes used to store the three significant digits
of the mantissa (4, 1, 2). Negative numbers include the sign in their length.

Taking this into account, the column size in bytes for a particular numeric
data value NUMBER (p), where p is the precision of a given value, can be
calculated using the following formula:

ROUND((length(p)+s)/2))+1

where s equals zero if the number is positive and s equals 1 if the number is
negative.

Zero and positive and negative infinity (only generated on import from Version
5 Oracle databases) are stored using unique representations. Zero and negative
infinity each require one byte; positive infinity requires two bytes.

Reference:

Oracle Concepts Manual