Number类型和float类型的比较

环境:

OS:Red Hat Linux As 5

DB:10.2.0.4

 

我们在设计表的时候数字的字段经常定义为number类型,很少用到浮点型和双精度浮点型,这可能跟业务需求有关,若这三种类型都能满足业务需求的情况下,推荐使用浮点型或是双精度浮点型,因为浮点型和双精度浮点类型是直接通过CPU计算的,而number类型是通过oracle软件计算的,我们知道CPU的计算速度要远大于软件的计算速度,下面通过一个简单的例子说明下.

 

1.创建表

Create Table tb_number_test
(
  number_type Number,
  float_type  binary_float,
  double_type binary_double
);

 

2.写入数据

SQL> Insert Into tb_number_test Select Rownum,Rownum,Rownum From Dba_Objects;

49746 rows created.

Elapsed: 00:00:01.79

3.测试各种类型的执行时间

SQL>set timing on;

SQL> Select sum(Sin(t.number_type)) From tb_number_test t;

SUM(SIN(T.NUMBER_TYPE))
-----------------------
             1.76424994

Elapsed: 00:00:11.73
SQL> Select sum(Sin(t.float_type)) From tb_number_test t;

SUM(SIN(T.FLOAT_TYPE))
----------------------
            1.764E+000

Elapsed: 00:00:00.23
SQL> Select sum(Sin(t.double_type)) From tb_number_test t;

SUM(SIN(T.DOUBLE_TYPE))
-----------------------
             1.764E+000

Elapsed: 00:00:00.17
从以上结果可以看出number类型用时最长,浮点类型和双精度浮点类型的用时差不多,双精度浮点类型用时最短.

为了准确性,再次执行看下结果

SQL> Select sum(Sin(t.number_type)) From tb_number_test t;

SUM(SIN(T.NUMBER_TYPE))
-----------------------
             1.76424994

Elapsed: 00:00:14.85
SQL> Select sum(Sin(t.float_type)) From tb_number_test t;

SUM(SIN(T.FLOAT_TYPE))
----------------------
            1.764E+000

Elapsed: 00:00:00.20
SQL> Select sum(Sin(t.double_type)) From tb_number_test t;

SUM(SIN(T.DOUBLE_TYPE))
-----------------------
             1.764E+000

Elapsed: 00:00:00.06

结果跟第一次的结果一致,说明number类型用时最长,双精度浮点类型用时最短.

 

目前数据库的数字类型的字段都定义为number了,那怎么用到浮点型呢,我们可以通过使用cast函数将number类型转换为浮点类型,如下:

 

SQL> Select sum(Sin(cast(t.number_type As binary_double))) From tb_number_test t;

SUM(SIN(CAST(T.NUMBER_TYPEASBINARY_DOUBLE)))
--------------------------------------------
                                  1.764E+000

Elapsed: 00:00:00.42

-- The End --