ROWID- Tables and Constraints

When 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 DBAs 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 are 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 it in the ROWIDs of offending records within the table. This affords you with an efficient way to capture and resolve issues with the offending data.

Tip: 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. Permanently using ROWID for the primary key is a mistake because the ROWIDs can change even without modifying the actual value. For this reason, instead of using ROWID for a primary key value, use a sequence-generated nonmeaningful number, such as an identity 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 does not 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 log files. An NCLOB is similar to a CLOB but allows for information encoded in the national character set of the database.

BLOBs are large amounts of binary data that usually are not meant to be human readable. Typical BLOB data include images, audio 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 is not feasible to store a large binary file within the database, then use a BFILE. BFILEs do not participate in database transactions and are not covered by Oracle security or backup and recovery. If you need those features, then use a BLOB and not a BFILE.

Creating a Table

The number of table features has expanded with 23c. There are several new SQL enhancements and a new data type with BOOLEAN, and the Oracle documentation has more than 200 pages covering CREATE and ALTER TABLE statements and table maintenance. Obviously, this chapter will not be covering all of the details. For most situations, you typically need to use only a fraction of the table options available. Listed next are the general factors that you should consider when creating a table:

• Type of table (heap organized, temporary, index-organized, partitioned and so on)

• Naming conventions

• Column data types and sizes

• Constraints (primary key, foreign keys, check)

• Index requirements (see Chapter 8 for more details)

• Initial storage requirements

• Special features (virtual columns, read-only, parallel, compression, no logging, invisible columns)

• Growth requirements

• Tablespace(s) for the table and its indexes

Before you run a CREATE TABLE statement, you need to give some thought to each item in the previous list. To that end, DBAs often use data modeling tools to help manage the creation of DDL scripts that are used to make database objects. Data modeling tools allow you to define visually tables and relationships and the underlying database features.