Oracle BINARY_FLOAT: 2 integers give same value?

Question

I have a table full of 10-digit integers and thought to speed up queries/math in Oracle by storing them as BINARY_FLOAT. That's more CPU-friendly than NUMBER and won't take as much space (I think), which means more data in memory.

However, it appears that BINARY_FLOAT yields the same bytes (and hence value) for two different numbers...which obviously won't work.

Example:

SQL> select dump(to_binary_float(25185387)) from dual;

DUMP(TO_BINARY_FLOAT(2518538
----------------------------
Typ=100 Len=4: 203,192,38,54

SQL> select dump(to_binary_float(25185388)) from dual;

DUMP(TO_BINARY_FLOAT(2518538
----------------------------
Typ=100 Len=4: 203,192,38,54

SQL> CREATE TABLE blah ( somenum BINARY_FLOAT );
Table created.

SQL> insert into blah (somenum) values (25185387);

1 row created.

SQL> insert into blah (somenum) values (25185388);

1 row created.

SQL> select somenum from blah;

   SOMENUM
----------
2.519E+007
2.519E+007

SQL> select to_number(somenum) from blah;

TO_NUMBER(SOMENUM)
------------------
          25185388
          25185388

SQL> select dump(somenum) from blah;

DUMP(SOMENUM)
------------------------------------------------------------------------------------------------------------------------
Typ=100 Len=4: 203,192,38,54
Typ=100 Len=4: 203,192,38,54

I expected that if I got into floating point, I might have some problem, but these are integers. I've tried various incantations - 25185387f, 25185387.0, 25185387*1.0, to_number(25185387), etc.

As I read the docs, BINARY_FLOAT should store to 1.79e308, so it can't be a rounding problem.

I'm using Oracle 11.2.0.3 on a 64-bit platform.

Ideas? Thanks.

Answer 1

Since the implementation of the oracle is BINARY_FLOAT standard ieee 754. BINARY_FLOAT is same as singe.

single have only 23 bits for significant bits.

25185387 = 11000000001001100011010 11 (length = 25)

25185388 = 11000000001001100011011 00 (length = 25)

hence the importance of these oracle rounds, discarding the least significant bits

25185387 ~ 11000000001001100011011 * 2^2

25185388 ~ 11000000001001100011011 * 2^2

so get the same value