Parting Shots

Parting Shots

Don't let the selectivity get lower than 10% for a B-tree. Use a bitmap index if selectivity goes below 1%.

A compound index should have no more than five columns.

A B-tree should have no more than five layers. If it gets beyond that, partition.

With Microsoft, every permanent big table should have a clustered index. With Oracle, the necessity is much less urgent.

Expect inconsistent performance if you have NULLs in your index keys, or if you UPDATE indexes frequently.

In a typical mixed environment, tables should have no more than five indexes. In a DSS environment, go higher—maybe up to 12 indexes per table. In a pure OLTP environment, go lower—make just enough indexes for a primary key and one or two foreign keys.

Just before the end of the month (or when you know there will be a flurry of activity), rebuild the indexes—leaving a big PCTFREE or a small FILLFACTOR (i.e., leave lots of room in the index pages so splits won't happen). Just after the end of the month (or when you're expecting report requests), add a few indexes—your shop is changing from an OLTP to a DSS for a while.

When all else is equal, do DELETEs before INSERTs within a transaction.

Never index a volatile column. A volatile column is one that will see changes to x% of the occurrences in the course of a week. Unfortunately, nobody can agree on the value of x. Clearly, though, the calculation should be based on this equation:


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows