Oracle Data Types: Tips for Choosing NUMBER Compared to BINARY DOUBLE?

Question

I am creating a scientific application that executes many crunch numbers in Java and C, and accuracy is critical. There are no crunches in the Oracle database (it is simply used to store variables between mathematical calculations).

I used a double-precision data type for all Java and C variables, which are largely based on IEEE 754. Thus, data written to a database and then read from a database is precision data types in C or Java.

What would you recommend using NUMBER or BINARY DOUBLE to store dual access data in Oracle?

For example, suppose I have a variable named X_Java that I write to the database as an X_DB variable as a BINARY DOUBLE. If I read this data in Java from a database and X_Java2 it in the variable X_Java2 , would X_Java exactly match X_Java2 ?

How will everything change, I saved X_DB in the database as NUMBER?

UPDATE 1: Please note that my criterion for "accuracy" is how close the number read from the database matches the amount that was available before it was written to the database.

On the one hand, I think that if the number available before it is written to the database is based on IEEE 754, then the data type used to store this value INSIDE the database should be accurate if this data type was also based on IEEE 754.

On the other hand, since a 64-bit number (double precision) can accurately store up to 16 (sometimes 17) precision digits, for storing in the form of NUMBERS in a database with 38 precision digits it should be accurately displayed to double precision. One of the drawbacks is that the NUMBER data type cannot store as large (or as small) values ​​as BINARY DOUBLE.

So my message.

Answer 1

For your convenience, I think BINARY DOUBLE might be the best. While NUMBER can support higher accuracy, additional transformations will be involved when inserting and extracting.

If you also need to support special IEEE754 numbers, such as positive / negative infinity or NaN, then this will certainly require a BINARY DOUBLE instead of NUMBER. Below is the documentation

Answer 2

From the Oracle documentation :

For number data type:

The NUMBER data type stores fixed and floating point. numbers of almost any size can be stored and guaranteed figurative among various systems working with Oracle Database, up to 38 digits accuracy .... Oracle guarantees portability of numbers using precision equal to or less than 38 digits.

For binary double / floating data type:

The Oracle database provides two numeric data types exclusively for floating point numbers: BINARY_FLOAT and BINARY_DOUBLE. They support all the basic functions provided by the NUMBER data type. However, while NUMBER uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE use binary precision. This allows faster arithmetic calculations and usually reduces storage requirements.

BINARY_FLOAT and BINARY_DOUBLE are approximate numeric data types. They store approximate representations of decimal values, not exact representations. For example, a value of 0.1 cannot be exactly represented by BINARY_DOUBLE or BINARY_FLOAT. They are often used for scientific computing. Their behavior is similar to the FLOAT and DOUBLE data types in Java and XMLSchema.

Based on the facts that you are not limited to a database number and are looking for a high precision search - I would think that a number is the best data type to use.

Answer 3

Why not save them as two column values? For each number, the mantissa is stored in the NUMBER column and the exponent in the BINARY_FLOAT or BINARY_DOUBLE column. It gives you the maximum. accuracy (not accuracy) and amplitude range. When you need to use a number, extract the two values ​​from the table and "re-combine" them into one number using the JAVA / C code.

Answer 4

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

http://www.dba-oracle.com/plsql/t_plsql_binary_float.htm