Old indexes never die…

Recently I was asked to describe the “life” of an index where I work.  Here’s what I had to say:

Primary Key and Foreign Key columns are indexed at table creation.  We always try to make sure all foreign key columns are indexed.

Subsequent indexes are created in response to functional requests for queries where we determine that an index would be the best approach.  These kinds of indexes are often Function-Based usually for space reasons (or to keep the index as small as possible).  Sometimes we use normal indexes, sometimes we use parallel full table scans if the data distribution and physical distribution makes the index impractical.  We’ll also see if we need a histogram on the column at this point.  We try to avoid hinting to use the new index.  Occasionally we compress an index.  Sometimes we determine that we should rebuild the table as an IOT.

Indexes are usually destroyed during space analysis where we do periodic reviews of space to see what we can drop and/or reorganize/move.  For example, our tablespaces have gone from being DMTs, to uniform-extent LMTs, to auto-allocate LMTs.  During each transition we evaluate whether or not we still need the index (and/or some tables).  Usually we drop it if we can prove that the index, even if it was in use, was non-useful.  That is, selects via equally aren’t the best way to get at the data.

Finally we’ve been slowly partitioning our largest tables which are basically historical time based INSERT tables.  Here, all indexes are going local, so it’s another opportunity for index review.

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder