Optimizing the Physical Design - Troubleshooting Oracle Performance - page 652

Since the internal format is proprietary, a CPU can't directly process values stored as NUMBER using the hardware
floating-point unit. Instead, they're processed by internal Oracle library routines. For this reason, the NUMBER datatype
isn't efficient when supporting number-crunching loads. To solve this problem, BINARY_FLOAT and BINARY_DOUBLE are
available. Their key advantage over the NUMBER datatype is that they implement the IEEE 754 standard, so a CPU can
directly process them. Their key disadvantage is that they are based on binary floating-point. Therefore, these types
cannot accurately represent decimal fractions that are so frequently used in commercial and financial applications.
Table 16-1 summarizes the key differences between these three datatypes.

Table 16-1. Comparing Number Datatypes
Property NUMBER(precision, scale) BINARY_FLOAT BINARY_DOUBLE

Range of values ±1.0E126 ±3.40E38 ±1.79E308

Size 1-22 bytes 4 bytes 8 bytes

Support ±infinity Yes Yes Yes

Support NAN No Yes Yes

Advantages Accuracy Speed Speed

Precision and scale can be specified Fixed length Fixed length

Character Strings
There are three basic datatypes used for storing character strings: VARCHAR2 , CHAR , and CLOB . The first two support up
to 4,000 and 2,000 bytes, respectively (note that the maximum length is specified in bytes, not characters). The third
supports up to several terabytes of data (the actual value depends on the default block size). The main difference
between VARCHAR2 and CHAR is that the former is of variable length, while the latter is of fixed length. This means
CHAR is usually used when the length of the character string is known. However, my advice is to always use VARCHAR2
because it provides better performance than CHAR . CLOB should be used only when character strings are expected
to be larger than the maximum size of VARCHAR2 . From version 11.1 onward, there are two storage methods for CLOB :
basicfile and securefile . For better performance, you should use securefile .
When using VARCHAR2 and CHAR datatypes, the maximum length shouldn't be set too high unnecessarily. This is
because even though the full space might not be used, in some situations the database engine has to allocate
enough memory to store whatever maximum length you specify. Hence, large amounts of memory might be allocated
for nothing.
The three basic datatypes store character strings according to the database character set. In addition, three other
datatypes, NVARCHAR2 , NCHAR , and NCLOB , are available to store character strings according to the national character set
(a secondary Unicode character set defined at the database level). These three datatypes have the same characteristics
as the basic ones with the same name. Only their character set is different.
LONG is another character string datatype that has been deprecated in favor of CLOB . You should no longer use it;
it's provided for backward compatibility only.

■ From version 12.1 onward, you can increase the maximum size of VARCHAR2 , NVARCHAR2 , and RAW to
32,767 bytes by setting the max_string_size initialization parameter to extended . the drawback of doing so is that
the database engine silently uses lOB datatypes to support that larger maximum size. my advice is to leave the
max_string_size initialization parameter to the default ( standard ) and, if more space is required, to explicitly use lOB
datatypes.

Caution