numeric precision in Oracle

user_574187 0
Viewed 352

I have a table with a FLOAT column, as defined in Oracle docs. I insert a number with 52 binary digits (not counting the initial 1 which is implicit), yet I see that oracle requires only 50 digits to correctly store it. How could that be?

create table numeric_types2(
    f1 float(60)
);
insert into numeric_types2 SELECT BIN_TO_NUM(
    1,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,1
                                      ) FROM DUAL;

and then:

select to_char(cast(f1 as float(49)), 'XXXXXXXXXXXXXXXXX'),
       to_char(cast(f1 as float(50)), 'XXXXXXXXXXXXXXXXX'),
       to_char(cast(f1 as float(51)), 'XXXXXXXXXXXXXXXXX')
from root.numeric_types2;

return:

10000000000004,    10000000000001,    10000000000001

why is that? am i missing some elementary floating-point math?

floating-accuracy precision floating-point oracle sql
3 Answers

The docs also state:

The FLOAT data type is a subtype of NUMBER

So it's a number under the covers and

To convert from binary to decimal precision, multiply n by 0.30103

Plugging the numbers in:

49 * 0.30103 = 14.75047
50 * 0.30103 = 15.05150
51 * 0.30103 = 15.65356

So float(50) and float(51) correspond to number(16), whereas float(49) is number(15)

You can verify this by taking a dump of the values:

create table numeric_types2 (
    f1 float(60), n1 number
);
insert into numeric_types2 
with rws as (
  select BIN_TO_NUM(
    1,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,0,0,0,0,0,0,0,0,0,
    0,1) n from dual
)
  select n, n from rws;

select dump ( f1 ), dump ( n1 ), 
       dump ( cast ( n1 as float(50) ) ) df50, 
       dump ( cast ( n1 as float(49) ) ) df49
from   numeric_types2;

DUMP(F1)                                 DUMP(N1)                                 DF50                                     DF49                                  
Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=9: 200,46,4,60,97,28,38,5,98   Typ=2 Len=8: 200,46,4,60,97,28,38,6

Note that

dump ( f1 ) = dump ( n1 ) = dump ( cast ( n1 as float(50) ) )

Only casting the number as float(49) gives a different value.

Finally note that the docs also contain this recommendation:

Oracle FLOAT is available for you to use, but Oracle recommends that you use the BINARY_FLOAT and BINARY_DOUBLE data types instead, as they are more robust

user_14859550

The linked doc page says

A FLOAT value is represented internally as NUMBER

Could it be that the number of binary digits is rounded to a number of decimal digits?

Also, converted to decimal, the hexadecimal numbers are:

10000000000001 -> 4503599627370497
10000000000004 -> 4503599627370500

So it looks to me like the number was converted to a decimal representation internally, rounded to 14 or 15 decimal digits - 497 would round to 500.

Note that in latin language, a digit is base-10 (digitus = finger).

user_13968220

As it already was said, this is because there's a number implementation underneath this type. And as you can check with the below query, binary precision is completely ignored:

  1. Binary precision is rounded up to the decimal precision N.
  2. The number is mathematically rounded to the first N significant positions (e.g. for N = 2: 101 becomes 100, 106 becomes 110).
  3. The number is stored in number format, having the first N significant digits from step 2 and scaled by the scale of the original number (e.g. the result has the same length, but N significant digits).
with a as (
  select level as l,
    cast(level as float(1)) as c,
    cast(level as float(2)) as c2,
    cast(level as float(3)) as c3,
    cast(level as float(4)) as c4,
    cast(level as float(6)) as c6,
    cast(level as float(7)) as c7
  from dual
  connect by level < 121
)
select a.*,
  dump(c, 16) as cd,
  dump(c4, 16) as c4d,
  dump(power(10, l), 16) as scale
from a

db<>fiddle here.

As you see, for precision 1-3 you can store only one digit, for 4-6 - two digits, etc. Also you can observe, that scale changes every two decimal digits (the first two bytes in dump, column scale).

So the storage is: two bytes for scale, and up to 20 bytes for precision, where each byte hold numbers from 0 to 99. But I cannot figure out where they've lost two digits at precision 38 (19*2, should be 20).

user_27787100
Related