Performance Considerations-Datatypes
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 70,000 rows and place the same data in there using the NUMBER and BINARY_FLOAT/BINARY_DOUBLE types as follows:
$ sqlplus eoda/foo@PDB1
SQL> create table t( num_type number,float_type binary_float,double_type binary_double);Table created.
SQL> insert /*+ APPEND */ into t select rownum, rownum, rownum from all_ objects;72089 rows created.
SQL> 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!
SQL> set numformat 999999.9999999999999999
SQL> select sum(ln(num_type)) from t;SUM(LN(NUM_TYPE))
SQL> select sum(ln(double_type)) from t;SUM(LN(DOUBLE_TYPE))
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.
Note If you are interested in the gory details of floating-point arithmetic and the subsequent loss of precision, see https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html.
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.