Oracle columns

[Kimathi Njeru]

This is a performance related question.
What factors should be considered when creating multiple (30-50) column tables?
I am trying to give input to my developers on the performance related issues that may occur when doing inserts/updates/deletes on a table that has 2-3 columns as opposed to one that has 20-30 columns?

[A. Clay Stephenson]

This is one of the classic it depends question. If the question is "Is one insert of a 30 column table faster than 10 inserts into tables of 3 columns each?" The answer is one table is much faster especially when you realize that you must have some sort of 'glue' (joins) to hold these 10 related tables.

The general answer to this question is make the number of columns larger but limit the number of indices. Performance tends to really suffer when the value of one column changes and that causes 15 indices to be altered.

[Bill Thorsteinson · Accepted solution]

General rules of thumb:

Primary key columns first.
Foreign key columns next.
Frequently searched columns next.
Frequently updated columns later.
Nullable columns last.
Least used nullable columns after more frequently used nullable columns.
Blobs in own table with few other columns.

Performance issues:

Chained rows (row doesn't fit in a block -> multiple reads).
Updating indexed columns.
Updating indexed columns in block.
Updates increasing row size (row migration or chaining).
Trailing null columns not written to database (less data).

Recommendations:

Normalize data.
Don't split tables for performance. (Possible
exception for tombstone data
versus high update derived
data, such as product info and
product inventory info).
Don't spend too much time tuning the database before
implementation.