ITPub博客

首页 > 数据库 > Oracle > Oracle10g的新数据类型:本地浮点数据类型

Oracle10g的新数据类型:本地浮点数据类型

原创 Oracle 作者:yaanzy 时间:2006-08-30 20:07:02 0 删除 编辑

Oracle在10gR1推出本地浮点数据类型(Native Floating-Point Data Types): BINARY_FLOAT(单精度32位)和BINARY_DOUBLE(双精度64位). 这些新数据类型都是基于IEEE二进制浮点运算标准,ANSI/IEEE Std 754-1985 [IEEE 754].

[@more@]

使用这些类型时要加上文字f(BINARY_FLOAT)或者d(BINARY_DOUBLE).比如,2.07f
、3.000094d

SQL和PL/SQL都支持这些新的数据类型:

SQL例子:

CREATE TABLE t (f BINARY_FLOAT, d BINARY_DOUBLE);
Table created.
INSERT INTO t VALUES (2.0f, 2.0d);
1 row created.
INSERT INTO t VALUES (1.5f/0.5f, 1.5d/0.5d);
1 row created.
INSERT INTO t VALUES (1.0f/0.0f, 1.0d/0.0d);
1 row created.

PL/SQL例子:

DECLARE
bf_var BINARY_FLOAT := 2.0f;
bd_var BINARY_DOUBLE := 2.0d;
BEGIN
bf_var := 1.5f/0.5f;
bd_var := 1.5d/0.5d;
END;
/


本地浮点数据类型最大的特点就是比NUMBER类型效率更高
- 硬件运算/数学运算快 5– 10 倍
- 占用更少的内存/磁盘空间(5/9 字节与 1 – 22 字节)
- BINARY_DOUBLE 值范围更大(e308 与 e125)
- 无需类型转换(使用与字节顺序无关的存储格式)

下面程序是使用欧拉级数计算圆周率∏:
∏ = sqrt ( 6 * ( 1 + 1/2*2 + 1/3*2 + ... ) )

方法一:使用NUMBER类型

create or replace procedure Euler_Pi_Number is

subtype My_Number is number;

zero constant My_Number := 0.0;
one constant My_Number := 1.0;
two constant My_Number := 2.0;
six constant My_Number := 6.0;
toler constant My_Number := 0.00000000001;
root_toler constant My_Number := toler/1000.0;

root My_Number;
prev_root My_Number;
prod_over_six My_Number;
prod My_Number;
pi My_Number;
prev_pi My_Number;
step My_Number;

begin
pi := one;
prev_pi := zero;
prod_over_six := zero;
step := zero;
while pi - prev_pi > toler
loop
prev_pi := pi;
step := step + one;
prod_over_six := prod_over_six + one/(step*step);
prod := six*prod_over_six;
prev_root := prod;
root := prod/two;
while Abs(root - prev_root) > root_toler
loop
prev_root := root;
root := (root + prod/root)/two;
end loop;
pi := root;
end loop;
end Euler_Pi_Number;
/


方法二:使用BINARY_DOUBLE类型

create or replace procedure Euler_Pi_Binary is

subtype My_Number is binary_double;

zero constant My_Number := 0.0d;
one constant My_Number := 1.0d;
two constant My_Number := 2.0d;
six constant My_Number := 6.0d;
toler constant My_Number := 0.00000000001d;
root_toler constant My_Number := toler/1000.0d;

root My_Number;
prev_root My_Number;
prod_over_six My_Number;
prod My_Number;
pi My_Number;
prev_pi My_Number;
step My_Number;

begin
pi := one;
prev_pi := zero;
prod_over_six := zero;
step := zero;
while pi - prev_pi > toler
loop
prev_pi := pi;
step := step + one;
prod_over_six := prod_over_six + one/(step*step);
prod := six*prod_over_six;
prev_root := prod;
root := prod/two;
while Abs(root - prev_root) > root_toler
loop
prev_root := root;
root := (root + prod/root)/two;
end loop;
pi := root;
end loop;
end Euler_Pi_Binary;
/


SQL> set timing on
SQL> exec Euler_Pi_Number;

PL/SQL 过程已成功完成。

已用时间: 00: 00: 11.59
SQL> exec Euler_Pi_Binary;

PL/SQL 过程已成功完成。

已用时间: 00: 00: 02.09


上面例子中近似300,000次迭代计算,NUMBER类型花费11.59秒,BINARY_DOUBLE类型花费约2.09秒,性能提高大约5.5倍.

结论:在版本10g之后写一些偏数字科学运行量巨大的存储过程、函数时,对于浮点数字类型要优先考虑使用本地浮点数据类型

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3898/viewspace-861920/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    108
  • 访问量
    762119