Oracle NUMBER(p) 存储大小? [英] Oracle NUMBER(p) storage size?

问题描述

我已经搜索过了，但我找不到我的问题的确切答案...

I've searched for it but i can't find a conclusive answer to my question...

我需要知道 Oracle 中 number(p) 字段的存储大小.

I need to know what is the storage size of a number(p) field in Oracle.

示例:NUMBER(1)、NUMBER(3)、NUMBER(8)、NUMBER(10) 等...

Examples: NUMBER(1), NUMBER(3), NUMBER(8), NUMBER(10) etc...

推荐答案

使用的存储空间取决于实际数值，以及列的精度和列的小数位数.

The storage used depends on the actual numeric value, as well as the column precision and scale of the column.

Oracle 11gR2 概念指南说:

Oracle 数据库以可变长度格式存储数字数据.每个值都以科学计数法存储，其中 1 个字节用于存储指数.数据库最多使用 20 个字节来存储尾数，尾数是包含其有效数字的浮点数的一部分.Oracle 数据库不存储前导零和尾随零.

Oracle Database stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent. The database uses up to 20 bytes to store the mantissa, which is the part of a floating-point number that contains its significant digits. Oracle Database does not store leading and trailing zeros.

10gR2 指南更进一步:

考虑到这一点，特定的列大小(以字节为单位)数值数据值 NUMBER(p)，其中 p 是给定的精度值，可以使用以下公式计算:

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

如果数字为正，则 s 等于 0，如果数字为正，则 s 等于 1数字是负数.

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

零和正负无穷大(仅在从版本 5 Oracle 数据库)使用唯一的表示进行存储.零和负无穷各需要 1 个字节；正无穷大需要 2 个字节.

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 1 byte; positive infinity requires 2 bytes.

如果您有权访问 My Oracle Support，请参阅说明 1031902.6 中的更多信息.

If you have access to My Oracle Support, there is more information in note 1031902.6.

你可以看到使用 vsize 或 <代码>转储.

create table t42 (n number(10));

insert into t42 values (0);
insert into t42 values (1);
insert into t42 values (-1);
insert into t42 values (100);
insert into t42 values (999);
insert into t42 values (65535);
insert into t42 values (1234567890);

select n, vsize(n), dump(n)
from t42
order by n;

          N   VSIZE(N)                           DUMP(N) 
------------ ---------- ---------------------------------
         -1          3           Typ=2 Len=3: 62,100,102 
          0          1                  Typ=2 Len=1: 128 
          1          2                Typ=2 Len=2: 193,2 
        100          2                Typ=2 Len=2: 194,2 
        999          3           Typ=2 Len=3: 194,10,100 
      65535          4          Typ=2 Len=4: 195,7,56,36 
 1234567890          6   Typ=2 Len=6: 197,13,35,57,79,91 

请注意，即使它们都在 number(10) 列中，存储也会因值而异，并且两个 3 位数字可能需要不同的存储量.

Notice that the storage varies depending on the value, even though they are all in a number(10) column, and that two 3-digit numbers can need different amounts of storage.

这篇关于Oracle NUMBER(p) 存储大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！