Table features for performance

*****************************************************
CHOOSING TABLE FEATURES FOR PERFORMANCE
*****************************************************

If a column always contains numeric data,make it a number data type.

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(38) For character data that is of variable length,use VARCHAR2 ( not VARCHAR).

Use DATE and TIMESTAMP data types appropriately. 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.

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.

Most tables should be created with a primary key.

Create a numeric surrogate key to be the primary key for each table. Populate the surrogate key from a sequence.

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 and allows for efficient retrieval of the logical key columns that may be frequently used in WHERE clauses

Define foreign keys where appropriate. It Enforces a business rule and keeps the data cleaner; helps optimizer choose efficient paths to data; prevents unnecessary table-level locks in certain DML operations

Consider special features such as virtual columns, 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.

**********************************************************
TABLE FEATURES THAT IMPACT SCALABILITY AND MAINTAINABILITY
**********************************************************
Use standards when naming tables, columns, constraints, triggers, indexes, and so on. It helps  document the application and simplifies maintenance.

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

Specify a separate tablespace for the table and indexes.

Let tables and indexes inherit storage attributes from the tablespaces.

Create primary-key constraints out of line. Allows you more flexibility when creating the primary key, especially if you have a situation where the primary key consists of multiple columns

Create comments for the tables and columns.

Avoid large object (LOB) data types if possible.

If you use LOBs in Oracle Database 11g or higher, use the new SecureFiles architecture.

If a column should always have a value, then enforce it with a NOT NULL constraint.

Create audit-type columns, such as CREATE_DTT and UPDATE_DTT, that are automatically populated with default values and/or triggers.

Use check constraints where appropriate.