DATATYPES in Objective-C

Printing ECC200 in Objective-C DATATYPES

The syntax for declaring columns of this type in a table is very straightforward: BINARY_FLOAT BINARY_DOUBLE That is it. There are no options to these types whatsoever.

In addition to the NUMBER, BINARY_FLOAT, and BINARY_DOUBLE types, Oracle syntactically supports the following numeric datatypes:

Note When I say syntactically supports, I mean that a CREATE statement may use these datatypes, but under

the covers they are all really the NUMBER type. There are precisely three native numeric formats in Oracle 10g Release 1 and above and only one native numeric format in Oracle9i Release 2 and earlier. The use of any other numeric datatype is always mapped to the native Oracle NUMBER type.

NUMERIC(p,s): Maps exactly to a NUMBER(p,s). If p is not specified, it defaults to 38. DECIMAL(p,s) or DEC(p,s): Maps exactly to a NUMBER(p,s). If p is not specified, it defaults to 38. INTEGER or INT: Maps exactly to the NUMBER(38) type. SMALLINT: Maps exactly to the NUMBER(38) type. FLOAT(p): Maps to the NUMBER type. DOUBLE PRECISION: Maps to the NUMBER type. REAL: Maps to the NUMBER type.

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 ops$tkyte@ORA11GR2> 2 ( num_type 3 float_type 4 double_type 5 ) 6 / create table t number, binary_float, binary_double

Table created. ops$tkyte@ORA11GR2> insert /*+ APPEND */ 2 select rownum, rownum, rownum 3 from all_objects 4 / 72089 rows created. ops$tkyte@ORA11GR2> 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 ------- -----total 4 cpu elapsed -------- ---------0.07 0.08 into t

select sum(ln(double_type)) from t call count ------- -----total 4 cpu elapsed -------- ---------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.

Note If you are interested in the gory details of floating-point arithmetic and the subsequent loss of precision,

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 ------- -----total 4 cpu elapsed -------- ---------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.