oracle的number的浅析

技术2025-08-11 7

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的数值。

取值范举例：

创建测试表t2SQL> create table t2 2 ( num_type number, 3 float_type binary_float, 4 double_type binary_double 5 );

Table created

插入测试数据1SQL> SQL> insert into t2 2 (num_type, float_type, double_type) 3 values 4 (1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321);

1 row inserted

查看测试数据1SQL> SQL> select to_char(num_type), 2 to_char(float_type, '999999999999.999999999'), 3 to_char(double_type, '99999999999.9999999999') 4 from t2 5 ;

插入测试数据2SQL> SQL> insert into t2 2 (num_type, float_type, double_type) 3 values 4 (12345678900987654321, 12345678900987654321, 12345678900987654321);

1 row inserted

查看测试数据2SQL> 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 123456789012345678900987654321 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 29999 10000 3 2999999 1000000 4 299999999 100000000 5 29999999999 10000000000 6 2999999999999 1000000000000 7 299999999999999 100000000000000 8 29999999999999999 10000000000000000 9 2999999999999999999 1000000000000000000 10 299999999999999999999 100000000000000000000 11 29999999999999999999999 10000000000000000000000 12 2999999999999999999999999 1000000000000000000000000 13 299999999999999999999999999 100000000000000000000000000 14 29999999999999999999999999999 10000000000000000000000000000 15 2999999999999999999999999999999 1000000000000000000000000000000 16 299999999999999999999999999999999 100000000000000000000000000000000 17 29999999999999999999999999999999999 10000000000000000000000000000000000 18 2999999999999999999999999999999999999 1000000000000000000000000000000000000 19 299999999999999999999999999999999999999 100000000000000000000000000000000000000 20 29999999999999999999999999999999999999999 1.0000000000000000000000000000000000E+40 21 2

数字1 数字2 数字1占字节数数字2占字节数--------------------------------------------------------------------------------------------------- ------------- -------------1.0000000000000000000000000000000000E+42 1.0000000000000000000000000000000000E+42 2 21.0000000000000000000000000000000000E+44 1.0000000000000000000000000000000000E+44 2 21.0000000000000000000000000000000000E+46 1.0000000000000000000000000000000000E+46 2 21.0000000000000000000000000000000000E+48 1.0000000000000000000000000000000000E+48 2 21.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 0Execute 1 0.00 0.00 0 0 0 0Fetch 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 0Execute 1 0.00 0.00 0 0 0 0Fetch 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 0Execute 1 0.00 0.00 0 0 0 0Fetch 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 0Execute 1 0.00 0.00 0 0 0 0Fetch 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-------

最新回复(0)