oracle 数字对比,oracle数字存储的性能对比

NUMBER vs  BINARY_FLOAT/BINARY_DOUBLE

Performance Considerations

In general, the Oracle NUMBER type is the best overall choice for most  applications. However, there are performance implications associated with that type. The Oracle NUMBER type is a software datatype—it is implemented in the Oracle software itself. We cannot use native hardware operations to add two NUMBER types together, as it is emulated in the software. The floating-point types, however, do not have this implementation. When we add two floating-point numbers together, Oracle will use the hardware to perform. the operation.

This is fairly easy to see. If we create a table that contains about 50,000 rows and place the same data in there using the NUMBER and BINARY_FLOAT/BINARY_DOUBLE types as follows

> create table t

2 ( num_type number,

3 float_type binary_float,

4 double_type binary_double

5 )

6 /

Table created.

> insert /*+ APPEND */ into t

2 select rownum, rownum, rownum

3 from all_objects

4 /

72089 rows created.

> commit;

Commit complete.

we then execute the same query against each type of column, using a complex mathematical function such as LN (natural log). We observe in a TKPROF report radically different CPU utilization:

select sum(ln(num_type)) from t

call count cpu elapsed

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

total 4 4.45 4.66

select sum(ln(float_type)) from t

call count cpu elapsed

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

total 4 0.07 0.08

select sum(ln(double_type)) from t

call count cpu elapsed

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

total 4 0.06 0.06

The Oracle NUMBER type used some 63 times the CPU of the floating-point types in this example. But, you have to remember that we did not receive precisely the same answer from all three queries!

ops$tkyte%ORA11GR2> set numformat 999999.9999999999999999

ops$tkyte%ORA11GR2> select sum(ln(num_type)) from t;

SUM(LN(NUM_TYPE))

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

734280.3209126472927309

ops$tkyte%ORA11GR2> select sum(ln(double_type)) from t;

SUM(LN(DOUBLE_TYPE))

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

734280.3209126447300000

The floating-point numbers were an approximation of the number, with between 6 and 13 digits of precision. The answer from the NUMBER type is much more precise than from the floats. However, when you are performing data mining or complex numerical analysis of scientific data, this loss of precision is typically acceptable, and the performance gain to be had can be dramatic.

It should be noted that in this case we can sort of have our cake and eat it, too. Using the built-in CAST function, we can perform. an on-the-fly conversion of the Oracle NUMBER type to a floating-point type, prior to performing the complex math on it. This results in a CPU usage that is much nearer to that of the native floating-point types:

select sum(ln(cast( num_type as binary_double ) )) from t

call count cpu elapsed

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

total 4 0.08 0.08

This implies that we may store our data very precisely, and when the need for raw speed arises, and the floating-point types significantly outperform. the Oracle NUMBER type, we can use the CAST function to accomplish that goal.