Database Reference
In-Depth Information
It should be noted that in this case we can sort of have our cake and eat it, too. Using the built-in
CAST
function,
we can perform an on-the-fly conversion of the Oracle
NUMBER
type to a floating-point type, prior to performing the
complex math on it. This results in a CPU usage that is much nearer to that of the native floating-point types:
select sum(ln(cast( num_type as binary_double ) )) from t
call count cpu elapsed
------- ------ -------- ----------
total 4 0.08 0.08
This implies that we may store our data very precisely, and when the need for raw speed arises, and the floating-point
types significantly outperform the Oracle
NUMBER
type, we can use the
CAST
function to accomplish that goal.
Long Types
LONG
types come in two flavors in Oracle:
A
•
LONG
text type capable of storing 2GB of text. The text stored in the
LONG
type is subject to
character set conversion, much like a
VARCHAR2
or
CHAR
type.
A
•
LONG RAW
type capable of storing 2GB of raw binary data (data that is not subject to character
set conversion).
The
LONG
types date back to version 6 of Oracle, when they were limited to 64KB of data. In version 7, they were
enhanced to support up to 2GB of storage, but by the time version 8 was released, they were superseded by the LOB
types, which we will discuss shortly.
Rather than explain how to use the
LONG
type, I will explain why you do not want to use the
LONG
(or
LONG RAW
)
type in your applications. First and foremost, the Oracle documentation is very clear in its treatment of the
LONG
types.
The
Oracle Database SQL Language Reference
manual states the following:
Do not create a table with
LONG
columns. Use
LOB
columns (
CLOB
,
NCLOB
,
BLOB
) instead.
LONG
columns
are supported only for backward compatibility.
Restrictions on LONG and LONG RAW Types
The
LONG
and
LONG RAW
types are subject to the restrictions outlined in Table
12-2
. Even though it might be considered
jumping ahead, I've added a column to say whether the corresponding
LOB
type, which is the replacement for the
LONG
/
LONG RAW
types, is subject to the same restriction.