Oracle data types: advice on choosing NUMBER versus BINARY DOUBLE?

Question

I'm generating a scientific application that performs a lot of number crunching in Java and C, and accuracy is critical. There is no number crunching done inside the Oracle database (it's merely used for storing variables between math computations).

I've used double-precision data type for all of my Java and C variables, which is largely based on IEEE 754. So, the data written into the database and then read out of the database will both be from double-precision data types in either C or Java.

What would you recommend I use to store the double-percision data in Oracle -- NUMBER or BINARY DOUBLE?

For example, let's say I have a variable called X_Java that I write into the database as variable X_DB as a BINARY DOUBLE. If I were to read this data back into Java from the database and store it in variable X_Java2, would X_Java exactly match X_Java2?

How would things change I stored X_DB in the database as a NUMBER?

UPDATE 1: Note that my benchmark for "accuracy" is how close the number read OUT OF the database is to the number that was available before being written INTO the database.

One one hand, I'd think that if the number available before being written INTO the database is based on IEEE 754, then the data type used to store this value INSIDE the database should be an exact match if that data type was also based on IEEE 754.

On the other hand, since a 64 bit (double precision) number can only accurately store up to 16 (sometime 17) digits of accuracy, then storing as a NUMBER in the database with 38 digits of precision should accurately map from and to double precision. One downside is that the NUMBER data type cannot store as large (or as small) values as BINARY DOUBLE.

Thus, my posting.

Solution 1

For your usecase I think BINARY DOUBLE might be the better match. While NUMBER could support higher precision, it would involve additional conversions when inserting and fetching.

If you also need to support special IEEE754 numbers like positive/negative infinity or NaN then that would definitely require BINARY DOUBLE instead of NUMBER. The following is from the oracle documentation on datatypes for 10.2

In a NUMBER column, floating point numbers have decimal precision. In a BINARY_FLOAT or BINARY_DOUBLE column, floating-point numbers have binary precision. The binary floating-point numbers support the special values infinity and NaN (not a number).

Note however that it does not support the distinction between positive and negative zero:

The new datatypes do not conform to IEEE754 in the following areas:

• -0 is coerced to +0.
• Comparison with NaN is not supported.
• All NaN values are coerced to either BINARY_FLOAT_NAN or BINARY_DOUBLE_NAN.
• Non-default rounding modes are not supported.
• Non-default exception handling mode are not supported.

Solution 2

From Oracle's documentation:

For Number datatype:

The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are guaranteed portable among different systems operating Oracle Database, up to 38 digits of precision. ... Oracle guarantees portability of numbers with a precision equal to or less than 38 digits.

For Binary Double/Float datatype:

Oracle Database provides two numeric datatypes exclusively for floating-point numbers: BINARY_FLOAT and BINARY_DOUBLE. They support all of the basic functionality provided by the NUMBER datatype. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.

BINARY_FLOAT and BINARY_DOUBLE are approximate numeric datatypes. They store approximate representations of decimal values, rather than exact representations. For example, the value 0.1 cannot be exactly represented by either BINARY_DOUBLE or BINARY_FLOAT. They are frequently used for scientific computations. Their behavior is similar to the datatypes FLOAT and DOUBLE in Java and XMLSchema.

Based off the facts that you are not number crunching in the databases, and are seeking high accuracy retrievals - I would think that Number is the better datatype to use.

Solution 3

Why not store them as two column values? For each number store the mantissa in a NUMBER column and the exponent in BINARY_FLOAT or BINARY_DOUBLE column. That gives you max. precision (not accuracy) and magnitude range. When you need to use the number, retrieve the two values from the table and "re-combine" them into one number using your JAVA/C code.

Comments

[Kirk Woll]

Technically, I think the more salient fact is that one is accurate for base-10 and one is accurate for base-2. You should use one or the other based on what base your own numbers are actually in, but most likely you're right that it's base-10.

[ggkmath]

Thanks Jorn, I'm not concerned about those special IEEE754 numbers. I just want the number in Java or C to be stored in the database so that it reads back as the same exact number into Java or C. While NUMBER does offer more precision, it seems this just consumes more storage without improving accuracy because the number going back into Java or C would simply strip off those extra precision digits anyway. I don't see a downside to using binary double, but since I've never seen it documented that binary double was intended for this purpose, it made me wonder..

[ggkmath]

But aren't the Java and C numbers base 2?

[Jörn Horstmann]

@ggkmath: Interesting thread, using binary double seems to require oracle specific jdbc methods and classes, which would be a reason against using them. The arguments about exactness are misleading imho, base 10 is not inherently more exact than other bases, you can't for example store 1/3, but you could in base 3.

[Jörn Horstmann]

I can't see how any other datatype than binary double could store a binary floating point number more exact. You just have to make sure there are no conversions to number or string on the way to the db, for example in the jdbc driver. But I should probably add that I haven't used binary double datatypes yet, most of the time I'm working with java's BigDecimal and Oracle's Number types.