Oracle Database 10g SQL (Osborne ORACLE Press Series)
The New Oracle10 g BINARY_FLOAT and BINARY_DOUBLE Types
Oracle10 g introduces two new data types: BINARY_FLOAT and BINARY_DOUBLE . BINARY_FLOAT stores a single precision 32-bit floating point number; BINARY_DOUBLE stores a double precision 64-bit floating point number. These new data types are based on the IEEE (Institute for Electrical and Electronic Engineering) standard for binary floating-point arithmetic.
Benefits of BINARY_FLOAT and BINARY_DOUBLE
BINARY_FLOAT and BINARY_DOUBLE are intended to be complementary to 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 may use up to 22 bytes.
-
Can represent a greater range of numbers BINARY_FLOAT and BINARY_ DOUBLE support numbers much larger and smaller than can be stored in a NUMBER .
-
Operations are typically performed faster Operations involving BINARY_FLOAT and BINARY_DOUBLE are typically performed faster than on NUMBER . This is because BINARY_FLOAT and BINARY_DOUBLE operations are typically performed in the hardware, whereas NUMBER s must first be converted using software before operations can be performed.
-
Closed operations Arithmetic operations involving BINARY_FLOAT and BINARY_DOUBLE are closed, which means that either a number or a special value is returned. For example, if you divide a BINARY_FLOAT by another BINARY_FLOAT , a BINARY_FLOAT is returned.
-
Transparent rounding BINARY_FLOAT and BINARY_DOUBLE use binary base-2 to represent a number, whereas NUMBER uses decimal base-10. The base used to represent a number affects how rounding occurs for that number. For example, a decimal floating-point number is rounded to the nearest decimal place, but a binary floating-point number is rounded to the nearest binary place.
Tip If you are developing a system that involves a lot of numerical computations , you should consider using BINARY_FLOAT and BINARY_DOUBLE to represent your numbers.
Using BINARY_FLOAT and BINARY_DOUBLE in a Table
The following statement creates a table named binary_test that contains a BINARY_FLOAT and BINARY_DOUBLE column:
CREATE TABLE binary_test (bin_float BINARY_FLOAT, bin_double BINARY_DOUBLE);
| Note | You ll find a script named oracle_10g_examples.sql in the SQL directory, which creates the binary_test table in the store schema. The script also performs the INSERT statements you ll see in this section. You can run this script if you have access to an Oracle10 g database. |
The following example adds a row to the binary_test table:
INSERT INTO binary_test (bin_float, bin_double) VALUES (39.5f, 15.7d);
Notice you use f and d to indicate a literal number is a BINARY_FLOAT or a BINARY_DOUBLE .
Special Values
In addition to literal values, you can also use the special values shown in Table 1-2 with a BINARY_FLOAT or BINARY_DOUBLE .
| Special Value | Description |
|---|---|
| BINARY_FLOAT_NAN | Not a number ( NaN ) for BINARY_FLOAT type |
| BINARY_FLOAT_INFINITY | Infinity ( INF ) for BINARY_FLOAT type |
| BINARY_DOUBLE_NAN | Not a number ( NaN ) for BINARY_DOUBLE type |
| BINARY_DOUBLE_INFINITY | Infinity ( INF ) for BINARY_DOUBLE type |
The following example inserts BINARY_FLOAT_INFINITY and BINARY_DOUBLE_INFINITY into the binary_test table:
INSERT INTO binary_test (bin_float, bin_double) VALUES (BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY);