The New Oracle10g BINARY_FLOAT and BINARY_DOUBLE Types

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 .

Table 1-2: Special Values

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);