oracle的number的淺析

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  ;

TO_CHAR(NUM_TYPE)               TO_CHAR(FLOAT_TYPE,'9999999999            TO_CHAR(DOUBLE_TYPE,'999999999
--------------------------- -----------------------------------------------------------------------------------
1234567890.0987654321            1234567940.000000000                        1234567890.0987654000

 

 

 

插入測試資料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-------