oracle的number的浅析

忘是亡心i 2022-09-28 15:27 323阅读 0赞

author：skate

time：2011-02-14

--------------------

**oracle的number的浅析**

从如下几个方面来认识number

1.表示的数值范围  
   2.占用的存储空间  
   3.number的性能

我们日常主要定义数值存储列是大都是用number，不过oracle也兼容一些以他类型，如下：

NUMERIC(p,s)：完全映射至NUMBER(p,s)。如果p未指定，则默认为38.  
DECIMAL(p,s)或DEC(p,s)：完全映射至NUMBER(p,s)。如果p为指定，则默认为38.  
INTEGER或INT：完全映射至NUMBER(38)类型。  
SMALLINT：完全映射至NUMBER(38)类型。  
FLOAT(b)：映射至NUMBER类型。  
DOUBLE PRECISION：映射至NUMBER类型。  
REAL：映射至NUMBER类型。

以上这些类型只是oracle在语法上支持的，在底层实际上还是number

1.表示的数值范围

NUMBER：Oracle NUMBER类型能以极大的精度存储数值，具体来讲，精度可达38位。其底层数据格式类似一种  
       “封包小数“表示。Oracle NUMBER类型是一种变长格式，长度为0～22字节。它可以存储小到10e-130、  
        大到（但不包括）10e126的任何数值。这是目前最为常用的数值类型。也是Oracle9i Release 2及以  
        前的版本只支持的唯一一种适合存储数值数据的固有数据类型，其他一起兼容类型只是一种和number  
        之间的映射，在底层实际上都是number

BINARY\_FLOAT：这是一种IEEE固有的单精度浮点数。它在磁盘上会占用5字节的存储空间：其中4个固定字节用  
              于存储浮点数，另外还有一个长度字节。BINARY\_FLOAT能存储有6为精度、范围在～±1038.53  
              的数值

BINARY\_DOUBLE：这是一种IEEE固有的双精度浮点数。它在磁盘上会占用9字节的存储空间：其中8个固定字节用  
               于存储浮点数，还有一个长度字节。BINARY\_DOUBLE能存储有12.位精度、范围在～±10308.25的  
               数值。

取值范举例：

创建测试表t2  
SQL>  create table t2  
  2   ( num\_type number,  
  3     float\_type binary\_float,  
  4     double\_type binary\_double  
  5   );

Table created

插入测试数据1  
SQL>  
SQL> insert into t2  
  2    (num\_type, float\_type, double\_type)  
  3  values  
  4    (1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321);

1 row inserted

查看测试数据1  
SQL>  
SQL>  select to\_char(num\_type),  
  2          to\_char(float\_type, '999999999999.999999999'),  
  3          to\_char(double\_type, '99999999999.9999999999')  
  4     from t2  
  5  ;

插入测试数据2  
SQL>  
SQL> insert into t2  
  2    (num\_type, float\_type, double\_type)  
  3  values  
  4    (12345678900987654321, 12345678900987654321, 12345678900987654321);

1 row inserted

查看测试数据2  
SQL>  
SQL>  select to\_char(num\_type),  
  2          to\_char(float\_type, '999999999999999999999'),  
  3          to\_char(double\_type, '999999999999999999999')  
  4     from t2  
  5  ;

TO\_CHAR(NUM\_TYPE)                        TO\_CHAR(FLOAT\_TYPE,'9999999999                                                   TO\_CHAR(DOUBLE\_TYPE,'999999999  
\---------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------  
1234567890.0987654321                                1234567940                                                                       1234567890  
12345678900987654321                       12345679400000000000                                                             12345678900987654000

SQL>

从测试结果可以看到，number可以正确显示数据，精度很高；binary\_float只正确的显示了前7位；binary\_double显示的数据范围和精度要比binary\_float高很多。

2.占用的存储空间

number类型占用0-22个字节，它实际上是磁盘上的一个变长数据类型，是oracle根据一定算法，采用尽可能少存储空间表示一个数

SQL> create table t ( x number, y number );

Table created

SQL>  
SQL>  insert into t ( x )  
  2   select to\_number(rpad('9',rownum\*2,'9'),'999999999999999999999999999999999999999999999999999999999')  
  3   from all\_objects  
  4   where rownum <= 25;

25 rows inserted

SQL> update t set y = x+1;

25 rows updated

SQL> column 数字1 format 9999999999999999999999999999999999999999999999999999999999999999999999999

SQL> column 数字2 format 9999999999999999999999999999999999999999999999999999999999999999999999999

SQL>  select to\_char(x) 数字1, to\_char(y) 数字2, vsize(x) 数字1占字节数, vsize(y) 数字2占字节数 from t order by x;

数字1                                             数字2                                       数字1占字节数 数字2占字节数  
\----------------------------------------------- ------------------------------------------------------------------------- ------------- -------------  
99                                              100                                             2             2  
9999                                            10000                                           3             2  
999999                                          1000000                                         4             2  
99999999                                        100000000                                       5             2  
9999999999                                      10000000000                                     6             2  
999999999999                                    1000000000000                                   7             2  
99999999999999                                  100000000000000                                 8             2  
9999999999999999                                10000000000000000                               9             2  
999999999999999999                              1000000000000000000                            10             2  
99999999999999999999                            100000000000000000000                          11             2  
9999999999999999999999                          10000000000000000000000                        12             2  
999999999999999999999999                        1000000000000000000000000                      13             2  
99999999999999999999999999                      100000000000000000000000000                    14             2  
9999999999999999999999999999                    10000000000000000000000000000                  15             2  
999999999999999999999999999999                  1000000000000000000000000000000                16             2  
99999999999999999999999999999999                100000000000000000000000000000000              17             2  
9999999999999999999999999999999999              10000000000000000000000000000000000            18             2  
999999999999999999999999999999999999            1000000000000000000000000000000000000          19             2  
99999999999999999999999999999999999999          100000000000000000000000000000000000000        20             2  
9999999999999999999999999999999999999999        1.0000000000000000000000000000000000E+40       21             2

数字1                                                                     数字2               数字1占字节数 数字2占字节数  
\--------------------------------------------------------------------------------------------------- ------------- -------------  
1.0000000000000000000000000000000000E+42         1.0000000000000000000000000000000000E+42      2             2  
1.0000000000000000000000000000000000E+44         1.0000000000000000000000000000000000E+44      2             2  
1.0000000000000000000000000000000000E+46         1.0000000000000000000000000000000000E+46      2             2  
1.0000000000000000000000000000000000E+48         1.0000000000000000000000000000000000E+48      2             2  
1.0000000000000000000000000000000000E+50         1.0000000000000000000000000000000000E+50      2             2

25 rows selected

SQL>

从例子可以看出，在oracle存储有效数据（非0数据）时，每增加两位数，数据的存储空间就增加一个字节，直到数据溢出。  
Oracle存储一个数时，会存储尽可能少的内容来表示这个数。为此会存储有效数字和用于指定小数点位置的一个指数，以及  
有关数值符号的信息（正或负）。因此，数中包含的有效数字越多，占用的存储空间就越大。

BINARY\_FLOAT与BINARY\_DOUBLE

浮点数用于近似数值；它们没有Oracle内置的 NUMBER类型那么精确。浮点数常用在科学计算中，由于允许在硬件（CPU、芯片）  
上执行运算，而不是在Oracle子例程中运算，所以在多种不同类型的应用中都很有用。因此，如果在一个科学计算应用中执行  
实数处理，算术运算的速度会快得多。

BINARY\_FLOAT在磁盘上会占用5字节的存储空间：其中4个固定字节用于存储浮点数，另外还有一个长度字节  
BINARY\_DOUBLE在磁盘上会占用9字节的存储空间：其中8个固定字节用于存储浮点数，另外还有一个长度字节

3.number的性能  
Oracle NUMBER类型对大多数应用来讲都是最佳的选择，尤其是经融行业，不过有利必有弊，number会带来性能的影响。  
因为Oracle NUMBER类型是一种软件数据类型，是在Oracle软件本身中实现。我们不能使用固有硬件操作将两个NUMBER  
类型相加，这要在软件中模拟，所以性能有很大的影响，为此，oracle又提供的两个浮点类型的BINARY\_FLOAT与BINARY\_DOUBLE。

下面举例说明性能对比

创建测试表  
SQL>  create table t2  
  2   ( num\_type number,  
  3     float\_type binary\_float,  
  4     double\_type binary\_double  
  5   );

Table created

SQL>  
SQL>  insert /\*+ APPEND \*/ into t2  
  2   select rownum, rownum, rownum  
  3   from all\_objects  
  4  ;

57302 rows inserted

SQL> alter session set events '10046 trace name context forever ,level 1';

Session altered

SQL>  select sum(ln(num\_type)) from t2;

SUM(LN(NUM\_TYPE))  
\-----------------  
 570510.312356972

SQL>  select sum(ln(float\_type)) from t2;

SUM(LN(FLOAT\_TYPE))  
\-------------------  
    570510.31235697

SQL>  select sum(ln(double\_type)) from t2;

SUM(LN(DOUBLE\_TYPE))  
\--------------------  
     570510.31235697

SQL>  select sum(ln(cast(num\_type as binary\_double ) )) from t2;

SUM(LN(CAST(NUM\_TYPEASBINARY\_D  
\------------------------------  
               570510.31235697

SQL> alter session set events '10046 trace name context off ';

Session altered

SQL>

查看跟踪文件内容如下：

........

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

select sum(ln(num\_type))  
from  
 t2

call     count       cpu    elapsed       disk      query    current        rows  
\------- ------  -------- ---------- ---------- ---------- ----------  ----------  
Parse        1      0.00       0.00          0          3          1           0  
Execute      1      0.00       0.00          0          0          0           0  
Fetch        1      2.31       2.25         38        193          0           1  
\------- ------  -------- ---------- ---------- ---------- ----------  ----------  
total        3      2.31       2.25         38        196          1           1

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

select sum(ln(float\_type))  
from  
 t2

call     count       cpu    elapsed       disk      query    current        rows  
\------- ------  -------- ---------- ---------- ---------- ----------  ----------  
Parse        1      0.00       0.00          0          1          0           0  
Execute      1      0.00       0.00          0          0          0           0  
Fetch        1      0.04       0.04          0        193          0           1  
\------- ------  -------- ---------- ---------- ---------- ----------  ----------  
total        3      0.04       0.04          0        194          0           1

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

select sum(ln(double\_type))  
from  
 t2

call     count       cpu    elapsed       disk      query    current        rows  
\------- ------  -------- ---------- ---------- ---------- ----------  ----------  
Parse        1      0.00       0.00          0          1          0           0  
Execute      1      0.00       0.00          0          0          0           0  
Fetch        1      0.03       0.04          0        193          0           1  
\------- ------  -------- ---------- ---------- ---------- ----------  ----------  
total        3      0.04       0.04          0        194          0           1

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

select sum(ln(cast(num\_type as binary\_double ) ))  
from  
 t2

call     count       cpu    elapsed       disk      query    current        rows  
\------- ------  -------- ---------- ---------- ---------- ----------  ----------  
Parse        1      0.00       0.00          0          1          0           0  
Execute      1      0.00       0.00          0          0          0           0  
Fetch        1      0.10       0.09          0        193          0           1  
\------- ------  -------- ---------- ---------- ---------- ----------  ----------  
total        3      0.10       0.10          0        194          0           1

从测试结果来看，number的性能确实很慢，比浮点类型BINARY\_FLOAT与BINARY\_DOUBLE慢57倍多，不过可以cast函数来转换下，  
在对number执行复杂数学运算之前先将其转换为一种浮点数类型，这样就会提高计算速度，但还是比直接用浮点类型慢很多，  
但也是一个折中的方法。

\--------end-------