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.
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.