Migrating from 9i to 11g – IEEE Floating point functions

This is new in Oracle 10g. BINARY_FLOAT and BINARY_DOUBLE data types stores floating point data in 32-bit IEEE format. Oracle suggests that using BINARY_FLOAT / BINARY_DOUBLE would be faster compared to NUMBER type.

BINARY_FLOAT & BINARY_DOUBLE requires less space for storing and also Oracle suggests that arithmetic operations can be faster with Binary float or double and is used for the following

* Column data types while table creation
* Index can be created on these columns
* Aggregation is supported in these columns
* They can be used in order by / group by clause
* storage of binary float / double is platform independent
* In-built functions like CIEL, ABS, ACOS, AVG, CORR, MAX, MIN etc.,
* Analytical functions are supported by floating point datatypes
* Not null, unique constraint, foreign key, check-point, REF constraint etc., can be created

Oracle 9i
SQL> create table test_num (a number, b number);

Table created.

Elapsed: 00:00:00.17

SQL> insert into test_num select rownum, rownum from dual connect by level <= 50000;

50000 rows created.

Elapsed: 00:00:01.14
select a*6000, b*300 + 950 / 150 from test_num;

Execution Plan
———————————————————-
Plan hash value: 950179409

——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 41368 | 1050K| 30 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_NUM | 41368 | 1050K| 30 (0)| 00:00:01 |
——————————————————————————

Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
28 recursive calls
0 db block gets
3499 consistent gets
0 physical reads
0 redo size
1169200 bytes sent via SQL*Net to client
67094 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed

****************************************************************************

Oracle 11g
SQL> create table test_float (a binary_float, b binary_double);

Table created.

Elapsed: 00:00:09.42

SQL> insert into test_float select rownum, rownum from dual connect by level <=
50000;

50000 rows created.

Elapsed: 00:00:01.48
ARITHMETIC CALCULATIONS:

SQL> select a*5000, b*400 + 650 / 250 from test_float;

50000 rows selected.

Elapsed: 00:00:01.00

Execution Plan
———————————————————-
Plan hash value: 30424090

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 54338 | 530K| 38 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TEST_FLOAT | 54338 | 530K| 38 (0)| 00:00:01|
Note
—–
– dynamic sampling used for this statement

Statistics
———————————————————-
28 recursive calls
0 db block gets
3537 consistent gets
0 physical reads
0 redo size
1267174 bytes sent via SQL*Net to client
67094 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed

SQL>