Oracle Illustrated

Feb 21 2010   7:39AM GMT

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



Posted by: Lakshmi Venkatesh
Tags:

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>

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: