13 Replies Latest reply: Mar 2, 2012 1:00 PM by tem RSS

    Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?

    tem
      My application does a lot of scientific number crunching. The math uses double precision variables in both C and Java (both based off of IEEE 754).

      If my goal is to store these variables in Oracle 11.2G database between computations such that the value read OUT OF the database is an exact match with the value used to write into the database, would you recommend storing such variables as an Oracle Data Type of NUMBER of BINARY DOUBLE?

      And, Why?

      Here I'm only concerned about accuracy (not speed nor storage).
        • 1. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
          JustinCave
          A NUMBER will be stored exactly. A BINARY_DOUBLE (or BINARY_FLOAT) is a floating point number that is inherently imprecise.

          If you read data into a floating point variable in either C or Java, however, you should not assume that it will be exactly identical to its value before being written to the database because floating point numbers are inherently imprecise. Just as you wouldn't expect the number to always be exactly the same after doing something like adding 1 and then subtracting 1, you shouldn't assume that persisting the data and reading it back won't result in slight rounding issues.

          If you're doing scientific number crunching, I assume that you're well aware of the issues around rounding in floating point numbers in general and how to account for the errors that introduces in your computations.

          Justin
          • 2. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
            tem
            Hi Justin,

            I begin my question having a number in Java or C already saved in double precision format. If we take this as the starting point, the definition of an "exact" match is always with respect to this starting number. Now, we write this number into the database in BINARY_DOUBLE. Since the variable written into the database and the stored value inside the database are both based on IEEE 754, wouldn't there be an exact match? I'd assume reading the variable out of the database into a double precision variable in Java or C should work the same but in reverse.

            Your answer talks about the rounding or error that may occur when any number (such as 0.1) gets mapped into an IEEE 754 floating point representation. But my questions starts with that mapped number (the one stored in IEEE 754 floating point). I have accounted for all errors in the actual number to get it into IEEE 754 format. Now, I simply wish to store this floating point value in the database so when I read it back it's the same value in Java or C. I want to minimize any error in this process with respect to the original floating point value that is used to write into the database.

            I would like to think Oracle created BINARY_DOUBLE exactly for this purpose, but then again, I'm new to databases so I really don't know.

            I know NUMBER allows like 38 digits of precision, but IEEE 754 floating point only uses say, 17 (at most) of these digits, so while one may think storing some extra digits (e.g. digits 18 through 38) inside the Oracle database provides greater accuracy, these extra digits in reality play no part (for accuracy or otherwise) in the actual computation once the NUMBER value is read back to double precision in Java or C program.
            • 3. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
              rp0428
              For your use case where you already have a floating point number you should store it that way in Oracle.

              A convertion from your floating point to an Oracle number and then back to floating point may, but may not, give you exactly the same floating point number you started with.
              • 4. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                JustinCave
                You understand, right, that if I have a floating point number in C (or Java) and I do
                my_old_float := 0.1;
                my_new_float := my_old_float + 1 - 1;
                that I cannot be certain that my_old_float will exactly match my_new_float, right? Even though mathematically the two clearly must be equal, floating point manipulation always introduces the potential for rounding. That's why you should never ever test two floating point numbers for exact equality.

                Given that, I would not assume that you could ever get exactly the same number back whether you just assign it to a different variable or write it to a file or write it to a database. You might, but you shouldn't bank on them being exactly the same.

                Justin
                • 5. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                  rp0428
                  Since you are working with Java see my reply to this Java thread on the different precision between Java Float and Double. The simple long division I show will help you understand why a conversion from Float to Number and back to Float may drop some of the scale.

                  This is due to Number being stored in decimal format (base 10) and Float being stored in binary.

                  See my reply Posted: Feb 16, 2012 2:25 PM in response to: 847102

                  Re: floating points
                  • 6. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                    tem
                    Thanks Justin, Yes, I understand your points regarding my_old_float and my_new_float math.

                    Even double precision in Java and C have digits well past the 17th (decimal) digit (I forget the exact number of digits, but there's over 30). I guess what I mean by exact is with respect to the first 17 (decimal) digits, since the others have little or no effect during computations.

                    OK, I'll concede we won't get a perfect match for all the digits -- just trying to understand if BINARY DOUBLE would be more accurate than NUMBER for storing double precision values from/to the application server? Sounds like it is...

                    Edited by: 918175 on Mar 1, 2012 11:04 AM
                    • 7. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                      tem
                      Hi rp0428, thanks for your comments.
                      A convertion from your floating point to an Oracle number and then back to floating point may,
                      but may not, give you exactly the same floating point number you started with.
                      I guess to Justin's point, neither will converting from Java floating point to Oracle floating point and then back again to Java floating point (that is, the final result may be different than the initial result).

                      However, it sounds like going through this process using BINARY_DOUBLE rather than NUMBER would provide better accuracy (for the reasons mentioned above, including your Java link above). Would you agree?
                      • 8. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                        rp0428
                        >
                        However, it sounds like going through this process using BINARY_DOUBLE rather than NUMBER would provide better accuracy (for the reasons mentioned above, including your Java link above). Would you agree?
                        >
                        Yes I would agree. If you are only using Oracle to STORE the data and do not usually actually access it or query on it the storing it as RAW(4) or RAW(8) will not change it at all.

                        You can still get the float or other value you need using the UTL_RAW function.

                        See the UTL_RAW package functions in the PL/SQL Packages and Types doc
                        http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/u_raw.htm#i1004637
                        UTL_RAW.CAST_TO_BINARY_DOUBLE (
                           r          IN RAW
                           endianess  IN PLS_INTEGER DEFAULT 1) 
                        RETURN BINARY_DOUBLE;
                        
                        UTL_RAW.CAST_TO_BINARY_FLOAT (
                           r          IN RAW
                           endianess  IN PLS_INTEGER DEFAULT 1) 
                        RETURN BINARY_FLOAT;
                        SELECT UTL_RAW.CAST_TO_BINARY_DOUBLE(myRawBinaryDouble) from myTable;
                        • 9. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                          tem
                          Hi rp0428, Thanks for opening the possibility for RAW(). I'm not exactly sure what to do with it... do I treat it similarly as I'd treat NUMBER or BINARY_DOUBLE? I'm not doing any computations inside Oracle database (they're all done in the application server).

                          For example, when I write from Java or C into the database, would I take the double precision value from the application server and simply store it as RAW(8)? Then, when I wish to restore the value, I would read it from the database as RAW(8) and store it in the application server, for example, returned within the resultSet call, as double-precision in Java or C? If so, then I just need to simply substitute RAW(8) for BINARY_DOUBLE and proceed as usual (assuming JDBC, OCILIB, etc. support RAW(8) just like they do NUMBER and BINARY_DOUBLE, etc.).

                          Or, do I need to do a UTL_RAW conversion from double precision in the app server to BINARY_DOUBLE in the database then to RAW(8) format in the database when storing into the database. Then, when retrieving from the database, use UTL_RAW to convert from RAW(8) format to BINARY_DOUBLE in the database before sending the BINARY_DOUBLE into the resultSet call issued by the application server? If so, then the database conversion step from/to BINARY_DOUBLE would color the data similarly as if I had just kept everything BINARY_DOUBLE to begin with, and then I don't see an advantage going to RAW.

                          Edited by: 918175 on Mar 1, 2012 12:02 PM
                          • 10. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                            JustinCave
                            918175 wrote:
                            Even double precision in Java and C have digits well past the 17th (decimal) digit (I forget the exact number of digits, but there's over 30). I guess what I mean by exact is with respect to the first 17 (decimal) digits, since the others have little or no effect during computations.
                            If you just care about the first 17 decimal digits being the same after persisting the data, it shouldn't matter whether you use NUMBER or BINARY_DOUBLE (or RAW). I'd strongly prefer NUMBER or BINARY_DOUBLE because then you can potentially use the data in the future outside of your application if someone wants to write a report.

                            Justin
                            • 11. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                              BobLilly
                              If you want to work with the binary_double datatype, then you should carefully read the documentation for the connectivity interface you are using. With JDBC, for example, you need to make sure you use the correct method--setDouble will convert the data to Oracle NUMBER, so you should be using setBinaryDouble to prevent conversion issues (see the 11g JDBC Developer's Guide). Similar issues may exist for other interfaces.

                              Regards,
                              Bob
                              • 12. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                                rp0428
                                >
                                would I take the double precision value from the application server and simply store it as RAW(8)?
                                >
                                That's what you can do
                                With JDBC you work with the RAW directly with Oracle
                                //   Construct a RAW from a byte array.
                                 oracle.sql.RAW myRaw = new oracle.sql.RAW((byte[] raw_bytes);
                                and use statement.setRaw to set the value or getRaw to get it
                                In your Java code you can use two converter functions. This example is for 'float' but double is similar. One function converts to a byte array and you use the byte array with the RAW constructor I showed above.
                                public static byte [] float2ByteArray (float f) {
                                    java.nio.ByteBuffer bb = java.nio.ByteBuffer.wrap(new byte [4]);
                                    bb.putFloat(f); 
                                    return bb.array();
                                    }
                                The other function converts the bytes array (myRaw.getBytes) to a float
                                public static float byteArray2Float(byte[] b) {   
                                    java.nio.ByteBuffer buf = java.nio.ByteBuffer.wrap(b);   
                                    return buf.getFloat();   
                                }
                                • 13. Re: Oracle Data Type: on storing data as NUMBER versus BINARY_DOUBLE?
                                  tem
                                  Very helpful. Thanks so much.