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 highermaybe up to 12 indexes per table. In a pure OLTP environment, go lowermake 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 indexesleaving 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 indexesyour 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: