When creating tables in Oracle Database 12c, you want to implement appropriate data types so as to maximize performance, scalability, and maintainability.
There are several performance and sustainability issues that you should consider when determining which data types to use in tables. Table 1 describes features specific to performance.
Table 1. Data Type Features That Impact Performance
If a column always contains numeric data and can be used in numeric computations, then make it a number data type. Keep in mind you may not want to make some columns that only contain digits as numbers (such as U.S. zip code or SSN).
Enforces a business rule and allows for the greatest flexibility, performance, and consistent results when using Oracle SQL math functions (which may behave differently for a “01” character vs. a 1 number); correct data types prevent unnecessary conversion of data types.
If you have a business rule that defines the length and precision of a number field, then enforce it—for example, NUMBER(7,2). If you don’t have a business rule, make it NUMBER.
Enforces a business rule and keeps the data cleaner; numbers with a precision defined won’t unnecessarily store digits beyond the required precision. This can affect the row length, which in turn can have an impact on I/O performance.
For most character data (even fixed length) use VARCHAR2 (and not CHAR).
The VARCHAR2 data type is more flexible and space efficient than CHAR. Having said that, you may want to use a fixed length CHAR for some data, such as a country iso-code.
If you have a business rule that specifies the maximum length of a column, then use that length, as opposed to making all columns VARCHAR2(4000).
Enforces a business rule and keeps the data cleaner.
Appropriately use date/time-related data types such as DATE, TIMESTAMP, and INTERVAL.
Enforces a business rule, ensures that the data is of the appropriate format, and allows for the greatest flexibility and performance when using SQL date functions and date arithmetic.
Avoid large object (LOB) data types if possible.
Prevents maintenance issues associated with LOB columns, like unexpected growth, performance issues when copying, and so on.
Note Prior to Oracle Database 12c the maximum length for a VARCHAR2 and NVARCHAR2 was 4,000, and the maximum length of a RAW column was 2,000. Starting with Oracle Database 12c, these data types have been extended to accommodate a length of 32,767.
How It Works
When creating a table, you must specify the columns names and corresponding data types. As a developer or a DBA, you should understand the appropriate use of each data type. We’ve seen many application issues (performance and accuracy of data) caused by the wrong choice of data type. For instance, if a character string is used when a date data type should have been used, this causes needless conversions and headaches when attempting to do date math and reporting. Compounding the problem, after an incorrect data type is implemented in a production environment, it can be very difficult to modify data types, as this introduces a change that might possibly break existing code. Once you go wrong, it’s extremely tough to recant and backtrack and choose the right course. It’s more likely you will end up with hack upon hack as you attempt to find ways to force the ill-chosen data type to do the job it was never intended to do.
Having said that, Oracle supports the following groups of data types:
Tip The LONG and LONG RAW data types are deprecated and should not be used.
The data types in the prior bulleted list are briefly discussed in the following subsections.
There are four character data types available in Oracle:
VARCHAR2 data type is what you should use in most scenarios to hold character/string data. A
VARCHAR2 only allocates space based on the number of characters in the string. If you insert a one-character string into a column defined to be
VARCHAR2(30), Oracle will only consume space for the one character.
When you define a
VARCHAR2 column, you must specify a length. There are two ways to do this: BYTE and CHAR. BYTE specifies the maximum length of the string in bytes, whereas CHAR specifies the maximum number of characters. For example, to specify a string that contains at the most 30 bytes, you define it as follows:
To specify a character string that can contain at most 30 characters, you define it as follows:
In almost all situations you’re safer specifying the length using
CHAR. When working with multibyte character sets, if you specified the length to be
VARCHAR2(30 byte), you may not get predictable results, because some characters require more than 1 byte of storage. In contrast, if you specify
VARCHAR2(30 char), you can always store 30 characters in the string, regardless of whether some characters require more than 1 byte.
NCHAR data types are useful if you have a database that was originally created with a single-byte, fixed-width character set, but sometime later you need to store multibyte character set data in the same database.
Tip Oracle does have another data type named
VARCHAR. Oracle currently defines VARCHAR as synonymous with
VARCHAR2. Oracle strongly recommends that you use
VARCHAR), as Oracle’s documentation states that
VARCHARmight serve a different purpose in the future.
Use a numeric data typeto store data that you’ll potentially need to use with mathematic functions, such as SUM, AVG, MAX, and MIN. Never store numeric information in a character data type. When you use a
VARCHAR2 to store data that are inherently numeric, you’re introducing future failures into your system. Eventually, you’ll want to report or run calculations on numeric data, and if they’re not a numeric data type, you’ll get unpredictable and often wrong results.
Oracle supports three numeric data types:
For most situations, you’ll use the NUMBER data type for any type of number data. Its syntax is
where scale is the total number of digits, and precision is the number of digits to the right of the decimal point. So, with a number defined as
NUMBER(5, 2) you can store values +/–999.99. That’s a total of five digits, with two used for precision to the right of the decimal point.
Tip Oracle allows a maximum of 38 digits for a
NUMBERdata type. This is almost always sufficient for any type of numeric application.
What sometimes confuses developers and DBAs is that you can create a table with columns defined as
DECIMAL, and so on. These data types are all implemented by Oracle with a
NUMBER data type. For example, a column specified as
INTEGER is implemented as a
BINARY_FLOAT data types are used for scientific calculations. These map to the
FLOAT Java data types. Unless your application is performing rocket science calculations, then use the
NUMBER data type for all your numeric requirements.
BINARYdata types can lead to rounding errors that you won't have with NUMBER and also the behavior may vary depending on the operating system and hardware.
When capturing and reporting on date-related information, you should always use a
TIMESTAMP data type (and not
NUMBER). Using the correct date-related data type allows you to perform accurate Oracle date calculations and aggregations and dependable sorting for reporting. If you use a VARCHAR2 for a field that contains date information, you are guaranteeing future reporting inconsistencies and needless conversion functions (such as
DATE data type contains a date component as well as a time component that is granular to the second. If you don’t specify a time component when inserting data, then the time value defaults to midnight (0 hour at the 0 second). If you need to track time at a more granular level than the second, then use
TIMESTAMP; otherwise, feel free to use
TIMESTAMP data type contains a date component and a time component that is granular to fractions of a second. When you define a
TIMESTAMP, you can specify the fractional second precision component. For instance, if you wanted five digits of fractional precision to the right of the decimal point, you would specify that as:
The maximum fractional precision is 9; the default is 6. If you specify 0 fractional precision, then you have the equivalent of the
DATE data type.
The RAW data typeallows you to store binary data in a column. This type of data is sometimes used for storing globally unique identifiers or small amounts of encrypted data. If you need to store large amounts (over 2000 bytes) of binary data then use a BLOB instead.
If you select data from a
RAW column, SQL*Plus implicitly applies the built-in
RAWTOHEX function to the data retrieved. The data are displayed in hexadecimal format, using characters 0–9 and A–F. When inserting data into a RAW column, the built-in HEXTORAW is implicitly applied.
This is important because if you create an index on a RAW column, the optimizer may ignore the index, as SQL*Plus is implicitly applying functions where the RAW column is referenced in the SQL. A normal index may be of no use, whereas a function-based index using RAWTOHEX may result in a substantial performance improvement.
Sometimes when developers/DBAs hear the word
ROWID (row identifier), they often think of a pseudocolumn provided with every table row that contains the physical location of the row on disk; that is correct. However, many people do not realize that Oracle supports an actual
ROWID data type, meaning that you can create a table with a column defined as the type
There are a few practical uses for the ROWID data type. One valid application would be if you’re having problems when trying to enable a referential integrity constraint and want to capture the
ROWID of rows that violate a constraint. In this scenario, you could create a table with a column of the type
ROWID and store in it the ROWIDs of offending records within the table. This affords you an efficient way to capture and resolve issues with the offending data.
Never be tempted to use a
ROWID data type and the associated
ROWID of a row within the table for the primary key value. This is because the
ROWID of a row in a table can change. For example, an
MOVE command will potentially change every
ROWID within a table. Normally, the primary key values of rows within a table should never change. For this reason, instead of using
ROWID for a primary key value, use a sequence-generated non-meaningful number (or in 12c, use an auto-incrementing column to populate a primary key column).
Oracle supports storing large amounts of data in a column via a LOB data type. Oracle supports the following types of LOBs:
If you have textual data that don’t fit within the confines of a
VARCHAR2, then you should use a
CLOB to store these data. A
CLOB is useful for storing large amounts of character data, such as text from articles (blog entries) and log files. An
NCLOB is similar to a
CLOB but allows for information encoded in the national character set of the database.
BLOBs store large amounts of binary data that usually aren’t meant to be human readable. Typical
BLOB data include images, audio, word processing documents, pdf, spread sheets, and video files.
BLOBs are known as internal LOBs. This is because they are stored inside the Oracle database. These data types reside within data files associated with the database.
BFILEs are known as external
BFILE columns store a pointer to a file on the OS that is outside the database. When it’s not feasible to store a large binary file within the database, then use a
BFILEs don’t participate in database transactions and aren’t covered by Oracle security or backup and recovery. If you need those features, then use a
BLOB and not a