Oracle Floats vs Number

Question

I'm seeing conflicting references in Oracles documentation. Is there any difference between how decimals are stored in a FLOAT and a NUMBER types in the database?

As I recall from C, et al, a float has accuracy limitations that an int doesn't have. R.g., For 'float's, 0.1(Base 10) is approximated as 0.110011001100110011001101(Base 2) which equals roughtly something like 0.100000001490116119384765625 (Base 10). However, for 'int's, 5(Base 10) is exactly 101(Base 2).

Which is why the following won't terminate as expected in C:

float i;
i = 0;
for (i=0; i != 10; )
{
    i += 0.1
}

However I see elsewhere in Oracle's documentation that FLOAT has been defined as a NUMBER. And as I understand it, Oracle's implementation of the NUMBER type does not run into the same problem as C's float.

So, what's the real story here? Has Oracle deviated from the norm of what I expect to happen with floats/FLOATs?

(I'm sure it's a bee-fart-in-a-hurricane of difference for what I'll be using them for, but I know I'm going to have questions if 0.1*10 comes out to 1.00000000000000001)

Solution 1

Oracle's BINARY_FLOAT stores the data internally using IEEE 754 floating-point representation, like C and many other languages do. When you fetch them from the database, and typically store them in an IEEE 754 data type in the host language, it's able to copy the value without transforming it.

Whereas Oracle's FLOAT data type is a synonym for the ANSI SQL NUMERIC data type, called NUMBER in Oracle. This is an exact numeric, a scaled decimal data type that doesn't have the rounding behavior of IEEE 754. But if you fetch these values from the database and put them into a C or Java float, you can lose precision during this step.

Solution 2

The Oracle BINARY_FLOAT and BINARY_DOUBLE are mostly equivalent to the IEEE 754 standard but they are definitely not stored internally in the standard IEEE 754 representation.

For example, a BINARY_DOUBLE takes 9 bytes of storage vs. IEEE's 8. Also the double floating number -3.0 is represented as 3F-F7-FF-FF-FF-FF-FF-FF which if you use real IEEE would be C0-08-00-00-00-00-00-00. Notice that bit 63 is 0 in the Oracle representation while it is 1 in the IEEE one (if 's' is the sign bit, according to IEEE, the sign of the number is (-1)^s). See the very good IEEE 754 calculators at http://babbage.cs.qc.cuny.edu/IEEE-754/

You can easily find this if you have a BINARY__DOUBLE column BD in table T with the query:

select BD,DUMP(BD) from T

Now all of that is fine and interesting (maybe) but when one works in C and gets a numeric value from Oracle (by binding a variable to a numeric column of any kind), one typically gets the result in a real IEEE double as is supported by C. Now this value is subject to all of the usual IEEE inaccuracies.

If one wants to do precise arithmetic one can either do it in PL/SQL or using special precise-arithmetic C libraries.

For Oracle's own explanation of their numeric data types see: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i16209

Solution 3

Oracle's Number is in fact a Decimal (base-10) floating point representation... Float is just an alias for Number and does the exact same thing.

if you want Binary (base-2) floats, you need to use Oracle's BINARY_FLOAT or BINARY_DOUBLE datatypes.

link text

Solution 4

Bill's answer about Oracle's FLOAT is only correct to late version(say 11i), in Oracle 8i, the document says:

You can specify floating-point numbers with the form discussed in "NUMBER Datatype". Oracle also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:

FLOAT specifies a floating-point number with decimal precision 38, or binary precision 126. FLOAT(b) specifies a floating-point number with binary precision b. The precision b can range from 1 to 126. To convert from binary to decimal precision, multiply b by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

It sounds like a Quadruple precision(126 binary precision). If I am not mistaken, IEEE754 only requires b = 2, p = 24 for single precision and p = 53 for double precision. The differences between 8i an 11i caused a lot of confusion when I was looking into a conversion plan between Oracle and PostgreSQL.

Solution 5

Like the PLS_INTEGER mentioned previously, the BINARY_FLOAT and BINARY_DOUBLE types in Oracle 10g use machine arithmetic and require less storage space, both of which make them more efficient than the NUMBER type

• ONLY BINARY_FLOAT and BINARY_DOUBLE supports NAN values

-not precise calculations

View more solutions