Selecting Table Data Types Appropriately in Oracle Database 12c

Selecting Oracle 12c Data Types AppropriatelyProblem

When creating tables in Oracle Database 12c, you want to implement appropriate data types so as to maximize performance, scalability, and maintainability.

Solution

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 of contents[Show]


Table 1. Data Type Features That Impact Performance

Recommendation

Reasoning

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:

  • Character
  • Numeric
  • Date/Time
  • RAW
  • ROWID
  • LOB

 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.

 

Character

There are four character data types available in Oracle: VARCHAR2, CHAR, NVARCHAR2, and NCHAR.The 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:

varchar2(30 byte)

To specify a character string that can contain at most 30 characters, you define it as follows:

varchar2(30 char)

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.

The NVARCHAR2 and 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 VARCHAR2 (and not VARCHAR), as Oracle’s documentation states that VARCHAR might serve a different purpose in the future.

 

Numeric

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:

  • NUMBER
  • BINARY_DOUBLE
  • BINARY_FLOAT

For most situations, you’ll use the NUMBER data type for any type of number data. Its syntax is

NUMBER(scale, precision)

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 NUMBER data 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 INT, INTEGER, REAL, 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 NUMBER(38).

The BINARY_DOUBLE and BINARY_FLOAT data types are used for scientific calculations. These map to the DOUBLE and FLOAT Java data types. Unless your application is performing rocket science calculations, then use the NUMBER data type for all your numeric requirements.

 Note  The BINARY data 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.

 

Date/Time

When capturing and reporting on date-related information, you should always use a DATE or TIMESTAMP data type (and not VARCHAR2 or 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 TO_DATE and TO_CHAR).

The 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 DATE.

The 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:

TIMESTAMP(5)

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.

 

RAW

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.

 

ROWID

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 ROWID.

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 ALTER TABLE...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).

 

LOB

Oracle supports storing large amounts of data in a column via a LOB data type. Oracle supports the following types of LOBs:

  • CLOB
  • NCLOB
  • BLOB
  • BFILE

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.

CLOBs, NCLOBs, and 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 LOBs. 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 BFILE. 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 BFILE.

 

Вас заинтересует / Intresting for you:

Oracle Database and Instance d...
Oracle Database and Instance d... 507 views Masha Thu, 21 Jun 2018, 18:23:39
Oracle Database and Security: ...
Oracle Database and Security: ... 871 views sepia Mon, 26 Nov 2018, 12:20:58
Recovering from Undo Tablespac...
Recovering from Undo Tablespac... 451 views Aida Thu, 26 Jul 2018, 08:00:38
Choosing Oracle Database 12c T...
Choosing Oracle Database 12c T... 643 views Ded Thu, 23 Aug 2018, 13:33:50

Comments on Selecting Table Data Types Appropriately in Oracle Database 12c

Be the first to comment
Please login to comment