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等于零；如果数字为s，则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或dump一起使用的实际存储空间.

You can see the actual storage used with vsize or dump.

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屋！