Oracle Tip: Use IEEE 754 FLOAT and DOUBLE datatypes in Oracle 10g

by Scott Stephens | Jun 02, 2004 3:36:00 PM

Tags: Scott Stephens

Takeaway: For the sake of speed, Oracle stores numbers with the NUMBER datatype in an unusual format, with bytes in binary-coded decimal.

Oracle stores numbers with the NUMBER datatype in an unusual format, with bytes in binary-coded decimal (where the byte hex value 0x99 represents 99 decimal). ANSI standard datatypes such as INTEGER, FLOAT, and DOUBLE are aliases for the NUMBER datatype, with extra restrictions: INTEGER datatypes don't allow decimal, FLOAT, and DOUBLE to restrict the number of significant digits to fit in integer values.

The IEEE 754 standard format is the most common format for representing real numbers on most computer operating systems and programming languages. The internal format of floating point numbers is always stored in either 32-bit (FLOAT) or 64-bit (DOUBLE) values.

When data are fetched from the database into host programming languages, numbers must be converted from one format to another. There's a small chance of precision loss when converting numbers. Also, the process of converting from one type to another can affect application performance, with large sets of values being converted.

In Oracle 10g, the Oracle Call Interface, a column, or PL/SQL variable can be bound to BINARY_FLOAT or BINARY_DOUBLE datatypes. There's no need to convert between these datatypes to use the data in host programming environments, such as Java, when casting or converting values and precision is preserved.

The trade-off is speed vs. accuracy. Oracle internal numbers are able to store up to 38 decimal significant digits exactly. IEEE 754 floating point numbers can store only about seven significant digits in binary. IEEE numbers also have "gradual underflow" problems because binary values can often be repeating values. For example, 0.1 is an exact value in Oracle number representation but is a repeating decimal when converted to binary.

TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!