Problem
When creating tables, you want to implement the appropriate table features that maximize performance, scalability, and maintainability.
Solution
There are several performance and sustainability issues that you should consider when creating tables. Table 1 describes features specific to table performance.
Table 1. Table Features That Impact Performance
Recommendation | Reasoning |
Consider setting the physical attribute PCTFREE to a value higher than the default of 10% if the table initially has rows inserted with null values that are later updated with large values. If there are never any updates, considering setting PCTFREE to a lower value. | As Oracle inserts records into a block, PCTFREE specifies what percentage of the block should be reserved (kept free) for future updates. Appropriately set, can help prevent row migration/chaining, which can impact I/O performance if a large percent of rows in a table are migrated/chained. |
All tables should be created with a primary key (with possibly the exception of tables that store information like logs). | Enforces a business rule and allows you to uniquely identify each row; ensures that an index is created on primary key column(s), which allows for efficient access to primary key values. |
Consider creating a numeric surrogate key to be the primary key for a table when the real-life primary key is a large character column or multiple columns. | Makes joins easier (only one column to join) and one single numeric key results in faster joins than large character-based columns or composites. |
Consider using auto-incrementing columns (12c) to populate PK columns. | Saves having to manually write code and/or maintain triggers and sequences to populate PK and FK columns. However, one possible downside is potential contention with concurrent inserts. |
Create a unique key for the logical business key—a recognizable combination of columns that makes a row unique. | Enforces a business rule and keeps the data cleaner; allows for efficient retrieval of the logical key columns that may be frequently used in WHERE clauses. If the PK is a surrogate key, there will usually be at least one unique key that identifies the logical business key. |
Define foreign keys where appropriate. | Enforces a business rule and keeps the data cleaner; helps optimizer choose efficient paths to data. |
Consider creating indexes on foreign key columns. | Can speed up queries that often join on FK and PK columns and also helps prevent certain locking issues. |
Consider special features such as virtual columns, invisible columns (12c), read-only, parallel, compression, no logging, and so on. | Features such as parallel DML, compression, or no logging can have a performance impact on reading and writing of data. |
How It Works
The “Solution” section describes aspects of tables that relate to performance. When creating a table, you should also consider features that enhance scalability and availability. Often DBAs and developers don’t think of these features as methods for improving performance. However, building a stable and supportable database goes hand in hand with good performance. Table 2 describes best practices features that promote ease of table management.
Table 2. Table Features That Impact Scalability and Maintainability
Recommendation | Reasoning |
Use standards when naming tables, columns, views, constraints, triggers, indexes, and so on. | Helps document the application and simplifies maintenance. |
Specify a separate tablespace for different schemas. | Provides some flexibility for different backup and availability requirements. |
Let tables and indexes inherit storage attributes from the tablespaces (especially if you use ASSM created tablespaces). | Simplifies administration and maintenance. |
Create primary-key constraints out of line (as a table constraint). | Allows you more flexibility when creating the primary key, especially if you have a situation where the primary key consists of multiple columns. |
Use check constraints where appropriate. | Enforces a business rule and keeps the data cleaner; use this to enforce fairly small and static lists of values. |
If a column should always have a value, then enforce it with a NOT NULL constraint. | Enforces a business rule and keeps the data cleaner. |
Create comments for the tables and columns. | Helps document the application and eases maintenance. |
If you use LOBs in Oracle Database 11g or higher, use the new SecureFiles architecture. | SecureFiles is the recommended LOB architecture; provides access to features such as compression, encryption, and deduplication. |
