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: