SQL Performance Tuning

"If the aforesaid pickaninny's a king he takes precedence over you, and if he's notthen what's he doing here?"

The Prince of Wales to Germany's angry ambassador, regarding Tonga's king

There are potential advantages to storing all nullable or variable values at the end of the row:

• Oracle won't waste storage space on a NULL for a variable-length column at the end of a row.

• Any DBMS can calculate the column's offset within the row more quickly if no variable-length columns are before it that have to be skipped .

You need not change your table definitions to realize these advantages if the DBMS will automatically figure out the best column order when you execute CREATE TABLE (as, for example, Microsoft does) or if the DBMS has different criteria (for example, with IBM there's a slight advantage in putting the most volatile column at the end; see the section "IBM Logging" in Chapter 14, "Data Changes"). So put columns in the order that users will expect to see them when they SELECT * . Typically, this means that related columns are grouped together, and the primary key is on the left, followed by columns that are frequently used for searches.

Here's another tip. One table with 50 columns is better than 50 tables with one column each because:

• The per-column storage overhead is less than the per-row storage overhead.

• The position of the fiftieth column might be determinable at parse time.

• Fewer entries are in the system catalog.

Therefore, if you have a miscellaneous pile of N static (read-only) system values, store them horizontally (in one table) rather than vertically (in N tables). This switch to a horizontal rather than vertical viewpoint is sometimes called pivoting.

The Bottom Line: Column Order

Storing all nullable or variable values at the end of the row may have advantages, but you need not change your table definitions-some DBMSs will automatically figure out the best column order when you execute CREATE TABLE and other DBMSs have different criteria.

Recommendation: Put columns in the order that users will expect to see when they execute SELECT * : generally , related columns grouped together, and the primary key on the left, followed by columns that are frequently used for searches.