Database Reference
In-Depth Information
Non-Native Number Types
In addition to the
NUMBER
,
BINARY_FLOAT
, and
BINARY_DOUBLE
types, Oracle syntactically supports the following
numeric datatypes:
NUMERIC(p,s)
: Maps exactly to a
NUMBER(p,s)
. If
p
is not specified, it defaults to 38.
•
DECIMAL(p,s)
or
DEC(p,s)
: Maps exactly to a
NUMBER(p,s)
. If
p
is not specified,
it defaults to 38.
•
INTEGER
or
INT
: Maps exactly to the
NUMBER(38)
type.
•
SMALLINT
: Maps exactly to the
NUMBER(38)
type.
•
FLOAT(p)
: Maps to the
NUMBER
type.
•
DOUBLE PRECISION
: Maps to the
NUMBER
type.
•
REAL
: Maps to the
NUMBER
type.
•
■
When I say “syntactically supports,” I mean that a
CREATE
statement may use these datatypes, but under the
covers they are all really the
NUMBER
type. there are precisely three native numeric formats in Oracle 10
g
release 1 and
above and only one native numeric format in Oracle9
i
release 2 and earlier. the use of any other numeric datatype is
always mapped to the native Oracle
NUMBER
type.
Note
Performance Considerations
In general, the Oracle
NUMBER
type is the best overall choice for most applications. However, there are performance
implications associated with that type. The Oracle
NUMBER
type is a
software datatype
—it is implemented in the
Oracle software itself. We cannot use native hardware operations to add two
NUMBER
types together, as it is emulated
in the software. The floating-point types, however, do not have this implementation. When we add two floating-point
numbers together, Oracle will use the hardware to perform the operation.
This is fairly easy to see. If we create a table that contains about 70,000 rows and place the same data in there
using the
NUMBER
and
BINARY_FLOAT
/
BINARY_DOUBLE
types as follows:
EODA@ORA12CR1> create table t
2 ( num_type number,
3 float_type binary_float,
4 double_type binary_double
5 )
6 /
Table created.
EODA@ORA12CR1> insert /*+ APPEND */ into t
2 select rownum, rownum, rownum
3 from all_objects
4 /
72089 rows created.
EODA@ORA12CR1> commit;
Commit complete.
