Database Reference
In-Depth Information
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!
EODA@ORA12CR1> set numformat 999999.9999999999999999
EODA@ORA12CR1> select sum(ln(num_type)) from t;
SUM(LN(NUM_TYPE))
--------------------------
734280.3209126472927309
EODA@ORA12CR1> 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.
■
If you are interested in the gory details of floating-point arithmetic and the subsequent loss of precision,
Note
