Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 14, 2002 - 4:06 pm UTC

Last updated: January 08, 2019 - 1:31 am UTC

Version: 9201

Viewed 10K+ times! This question is

You Asked

Hi Tom
Tom
I am sure you have answered my question several times as " floats are synonyms for numbers. But I have a situation at work where my colleague wont agree with me that there is no difference between oracle float and number datatype with no precision. This is affecting my implemention in which I want to use Float as the datatype vs Number. THis is for conveneince in conversion of my scripts to Sql server where we want such columns to be converted to Float (Number column are converted to numeric in sql).

He did some test through JDBC and got the metadata for the float & number columns as
FLOAT Number(0)
Number Number(38)

IF you have a table t with (id Number, Code Float)

We are able to insert exactly 126 digits in both as follows:

insert into t values(123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456,
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456);

ID CODE
---------- ----------
1.235E+125 1.235E+125


set numwidth 50
Select * from t;

ID CODE
---------- ----------
1.2345678901234567890123456789012345678900000E+125
1.2345678901234567890123456789012345679000000E+125

So the float column has more precision.

Also if you execute the following

SQL> select * from xtest where num=fl;
no rows selected

Does this prove that in Oracle FLoat columns are different from Number columns.

Please help in solving this confusion.

Thanks


and Tom said...

Floats are numbers.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( id number, code float );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t
2 values(
3 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456,
4 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456)
5 /

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set linesize 50
ops$tkyte@ORA817DEV.US.ORACLE.COM> set numwidth 50
ops$tkyte@ORA817DEV.US.ORACLE.COM> select id, code, id-code from t;

ID
--------------------------------------------------
CODE
--------------------------------------------------
ID-CODE
--------------------------------------------------
1.2345678901234567890123456789012345678900000E+125
1.2345678901234567890123456789012345679000000E+125
-1.0000000000000000000000000000000000000000000E+87

Doesn't show that floats aren't numbers, they are just a number with a different precision then number in itself.

(it also shows that numbers in this particular case are capable of storing 39 digits -- this may vary a bit, 38 is all we assure you and that is what the float is actually storing. The float is less precise in this case and in your case, not the other way around).


In anycase, give me a REAL WORLD example where this difference makes a difference - for example, are you storing numbers with 39 digits of precision (if you are BEWARE, 38 is what we promise, not 39). If you are storing with 38, we can do.



Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

One conversion item to beaware of

Jim Kennedy, August 14, 2002 - 8:53 pm UTC

One problem is that certain operations in that the other database vendor might give different results when done in Oracle due to the differences in percision of the two vendors.

So if one vendor stores numbers up to 15 or 16 digits of percision and the other 38 or 39 then in some instances mathmatical operations / conversions will be different.

Tom Kyte
August 15, 2002 - 8:00 am UTC

Oh definitely -- 100%

All you get here is syntatic compatibility -- eg: the scripts will run.

But, you don't get application level "sameness".

The other vendors will only give you 7 to 13 digits typically as they use native 4 and 8 byte floats.

That is just the very teeny tiny tip of the iceberg as well. We won't even get into the differences in locking, concurrency, consistency, data integrity, etc.

SQL*Plus rounding off FLOAT?

A reader, October 24, 2006 - 1:44 pm UTC

Devl@1> create table t1(c1 float);

Table created.

Devl@1> insert into t1 values(233.81999999999999);

1 row created.

Devl@1> commit;

Commit complete.

Devl@1> select * from t1;

C1
----------
233.82

Devl@1> select dump(c1) from t1;

DUMP(C1)
--------------------------------------------------------------------------------

Typ=2 Len=10: 194,3,34,82,100,100,100,100,100,100

Devl@1>

The version is 10gr2 for both client and server. Is this expected behavior?

Thanks


Tom Kyte
October 24, 2006 - 2:09 pm UTC

yes, the default numformat rounds for display purposes.

set numformat 999999999.99999999999999999999999999

and do it again.

SQL*Plus rounding off FLOAT?

A reader, October 24, 2006 - 5:33 pm UTC

Thanks Tom. It works. I also tried set numwidth 50 that also works. From the docs, looks like numformat has a higher priroty so I will use numformat.


Tom Kyte
October 25, 2006 - 9:26 am UTC

use to_char() and you'll be even better off as you won't affect ALL numbers, just the one of interest.

Float or Number for £ Amt field

Anirban Chakraborty, June 15, 2011 - 7:24 am UTC

Hi Tom,
Just would like to understand your view on this.
Can use FLOAT instead for NUMBER (x,y) in £ Amount field? If they are same, will there be same round off error?

What is recommended for monetory columns please?
Tom Kyte
June 17, 2011 - 12:37 pm UTC

You cannot use floats or doubles for money, you'll lose (or gain magically) money that way.

floats cannot accurately hold money data.


Use the NUMBER type for money. You get 38 digits of precision.

ops$tkyte%ORA11GR2> create table t ( x number, y binary_float, z binary_double );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t (x) select 100.00+ rownum/100 from all_users;

44 rows created.

ops$tkyte%ORA11GR2> update t set y = x, z = x;

44 rows updated.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set numformat 99999999.9999999999
ops$tkyte%ORA11GR2> select x, y, z,
  2         case when x <> cast(y as number) then '*' end bad_y,
  3         case when x <> cast(z as number) then '*' end bad_z
  4    from t
  5   where x <> cast(y as number)
  6      or x <> cast(z as number);

                   X                    Y                    Z B B
-------------------- -------------------- -------------------- - -
      100.0100000000       100.0100020000       100.0100000000 * *
      100.0200000000       100.0199970000       100.0200000000 *
      100.0300000000       100.0299990000       100.0300000000 *
      100.0400000000       100.0400010000       100.0400000000 * *
      100.0500000000       100.0500030000       100.0500000000 *
      100.0600000000       100.0599980000       100.0600000000 *
      100.0700000000       100.0700000000       100.0700000000   *
      100.0800000000       100.0800020000       100.0800000000 *
      100.0900000000       100.0899960000       100.0900000000 *
      100.1000000000       100.0999980000       100.1000000000 * *
      100.1100000000       100.1100010000       100.1100000000 *
      100.1200000000       100.1200030000       100.1200000000 *
      100.1300000000       100.1299970000       100.1300000000 *
      100.1400000000       100.1399990000       100.1400000000 *
      100.1500000000       100.1500020000       100.1500000000 * *
      100.1600000000       100.1600040000       100.1600000000 *
      100.1700000000       100.1699980000       100.1700000000 *
      100.1800000000       100.1800000000       100.1800000000   *
      100.1900000000       100.1900020000       100.1900000000 *
      100.2000000000       100.1999970000       100.2000000000 *
      100.2100000000       100.2099990000       100.2100000000 * *
      100.2200000000       100.2200010000       100.2200000000 *
      100.2300000000       100.2300030000       100.2300000000 *
      100.2400000000       100.2399980000       100.2400000000 * *
      100.2600000000       100.2600020000       100.2600000000 * *
      100.2700000000       100.2699970000       100.2700000000 *
      100.2800000000       100.2799990000       100.2800000000 *
      100.2900000000       100.2900010000       100.2900000000 * *
      100.3000000000       100.3000030000       100.3000000000 *
      100.3100000000       100.3099980000       100.3100000000 *
      100.3200000000       100.3200000000       100.3200000000   *
      100.3300000000       100.3300020000       100.3300000000 *
      100.3400000000       100.3399960000       100.3400000000 *
      100.3500000000       100.3499980000       100.3500000000 * *
      100.3600000000       100.3600010000       100.3600000000 *
      100.3700000000       100.3700030000       100.3700000000 *
      100.3800000000       100.3799970000       100.3800000000 *
      100.3900000000       100.3899990000       100.3900000000 *
      100.4000000000       100.4000020000       100.4000000000 * *
      100.4100000000       100.4100040000       100.4100000000 *
      100.4200000000       100.4199980000       100.4200000000 *
      100.4300000000       100.4300000000       100.4300000000   *
      100.4400000000       100.4400020000       100.4400000000 *

43 rows selected.

Just found this little gem.

A reader, April 10, 2012 - 8:47 pm UTC

Very cool Tom.

I like your example immediately from creating the sample data right through to the CASE statement to show the comparision results to easily see where x <> y and x <> z.

I was just reading the following and wondering if the new data types offer smaller space, greater range of numbers and faster performance.

Thanks for highlighting that NUMBER is not the same a float etc

Benefits of BINARY_FLOAT and BINARY_DOUBLE
BINARY_FLOAT and BINARY_DOUBLE are intended to complement the existing NUMBER type. BINARY_FLOAT and BINARY_DOUBLE offer the following benefits over NUMBER:
Smaller storage required BINARY_FLOAT and BINARY_DOUBLE require 5 and 9 bytes of storage space, whereas NUMBER might use up to 22 bytes.

Greater range of numbers represented BINARY_FLOAT and BINARY_DOUBLE support
numbers much larger and smaller than can be stored in a NUMBER.

Faster performance of operations Operations involving BINARY_FLOAT and BINARY_DOUBLE are typically performed faster than NUMBER operations. 

This is because BINARY_FLOAT and BINARY_DOUBLE operations are typically performed in the hardware, where as NUMBERs must first be converted using software before operations can be performed.




Tom Kyte
April 11, 2012 - 11:25 am UTC

Hopefully that benefits section was immediately followed by a caveats/downsides section!

floats have a meager 6 digits of precision

doubles have a better 13 digits of precision, but still far away from the 38 given by number type

floats and doubles are incapable of representing *many* numbers - they just cannot hold them.

ops$tkyte%ORA11GR2> set numformat 9999.9999999999999999999999999999999999999
ops$tkyte%ORA11GR2> select 1.0101f, 1.0101 from dual;

                                    1.0101F
-------------------------------------------
                                     1.0101
-------------------------------------------
    1.0101000100000000000000000000000000000
    1.0101000000000000000000000000000000000




In SQL, the benefits of native operations don't really show up unless you are using very specific functions - like LN(). for example select sum(float) vs select sum(number) will not be very different (but select sum(ln(float)) will outperform select sum(ln(number)) greatly)

In PLSQL, if you are doing lots of computations - you'll find that floats/doubles outperform numbers - sometimes by a lot. But remember - lack of precision, inability to store certain numbers altogether make them useless for financial applications and the like.


You have to be *very* careful using them.

FLOATs vs floating-point datatypes

Lasse Jenssen, December 20, 2018 - 11:59 am UTC

One of the examples in this thread are speaking about FLOATs and are using floating-point datatypes (such as BINARY_FLOAT and BINARY_DOUBLE). The FLOAT datatype is just a subtype of NUMBER, and behaves the same as a NUMBER. The example where Tom shows the lack of accuracy is about floating-point datatypes, not the datatype FLOAT. Isn't this correct?
Chris Saxon
December 20, 2018 - 5:51 pm UTC

FLOAT is a subtype of number. But its precision is in binary digits. So you can end up with truncation you wouldn't get with NUMBER:

From the docs:

create table t (col1 number(5,2), col2 float(5));

insert into t values (1.23, 1.23);
insert into t values (7.89, 7.89);
insert into t values (12.79, 12.79);
insert into t values (123.45, 123.45);

select * from t;

COL1     COL2   
    1.23    1.2 
    7.89    7.9 
   12.79     13 
  123.45    120 

Understand the math

Mike Tefft, January 07, 2019 - 8:34 pm UTC

NUMBER uses base 10 but BINARY_DOUBLE uses base 2. See https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Types.html#GUID-9401BC04-81C4-4CD5-99E7-C5E25C83F608


This is important to understand because not all Base-10 fractions can be represented exactly as fractions in Base-2 (although all base-2 fractions can be represented exactly in Base-10, assuming you use enough precision)

A Base-10 fraction is essentially

some_integer/(10**some_exponent)

which is the same as

some_integer/((5**some_exponent)*(2**some_exponent))


But a base-2 fraction can only be

some_integer/(2**some_exponent)

Some Base-10 fractions, like 3.5, can be reduced from (35)/((2**1)*(5**1)) to 7/(2**1) so there is an exact representation of 3.5 under Base-2. But a number like 3.2 can not be reduced to a form that has only some power of 2 in its denominator (required for an exact representation under Base 2) so the best you can get is an approximation:

09:57:33 SQL> select to_char(cast(3.2 as binary_double),'9.99999999999999999999') from dual;

TO_CHAR(CAST(3.2ASBINAR
-----------------------
3.20000000000000020000

If you are dealing with money, which requires accurate representations of base-10 fractions (like .35) then you must use a base-10 representation like NUMBER, preferably with a specific precision. Otherwise you are introducing a defect.
Connor McDonald
January 08, 2019 - 1:31 am UTC

nice input.