Database Reference
In-Depth Information
That last fact explains why it is useful to know that numbers are stored in varying width fields. When attempting
to size a table (e.g., to figure out how much storage 1,000,000 rows would need in a table), you have to consider the
NUMBER
fields carefully. Will your numbers take 2 bytes or 20 bytes? What is the average size? This makes accurately
sizing a table without representative test data very hard. You can get the worst-case size and the best-case size, but the
real size will likely be some value in between.
BINARY_FLOAT/BINARY_DOUBLE Type Syntax and Usage
Oracle 10
g
introduced two numeric types for storing data; they are not available in any release of Oracle prior
to version 10
g
. These are the IEEE standard floating-points many programmers are used to working with. For a
full description of what these number types look like and how they are implemented, I suggest reading
http://en.wikipedia.org/wiki/Floating-point
.
It is interesting to note the following in the basic definition
of a floating-point number in that reference (emphasis mine):
A floating-point number is a digital representation for a number in a certain subset of the rational
numbers, and is often used
to approximate
an arbitrary real number on a computer. In particular,
it represents an integer or fixed-point number (the significand or, informally, the mantissa)
multiplied by a base (usually 2 in computers) to some integer power (the exponent). When the base
is 2, it is the binary analogue of scientific notation (in base 10).
They are used to
approximate
numbers; they are not nearly as precise as the built-in Oracle
NUMBER
type
described previously. Floating-point numbers are commonly used in scientific applications and are useful in many
types of applications due to the fact that they allow arithmetic to be done in hardware (on the CPU, the chip) rather
than in Oracle subroutines. Therefore, the arithmetic is much faster if you are doing real number-crunching in a
scientific application, but you would not want to use floating-points to store financial information. For example,
suppose you wanted to add together 0.3 and 0.1 as floats. You might think the answer is of course 0.4. You would be
wrong (in floating point arithmetic). The answer is a little bit larger than 0.4:
EODA@ORA12CR1> select to_char( 0.3f + 0.1f, '0.99999999999999' ) from dual;
TO_CHAR(0.3F+0.1F
-----------------
0.40000000600000
This is not a bug, this is the way IEEE floating point numbers work. As a result, they are useful for a certain
domain of problems, but definitely not for problems where dollars and cents count!
The syntax for declaring columns of this type in a table is very straightforward:
BINARY_FLOAT
BINARY_DOUBLE
That is it. There are no options to these types whatsoever.
