Re: Thumb Rule For Indexing

On Thu, 17 Feb 2005 02:40:55 -0800, shanebeast wrote:

> Hi All,
>
> I'm totally new to Oracle DBA. I've got a question is there any thumb
> rule to pick up the columns for indexing.

My personal rules of thumb.

Rule 0:

 Decide whether you are thinking about indexes for performance or for  business rule/constraint purposes. (For example, uniqueness can be  enforced using an index.) If business rule, proceed when you understand  why it's helping. If performance, read on ...

Rule 1:

 Don't index unless there is a proven need for the index. Each index has a  resource and performance implication in both query and update, and that  implication needs to be evaluated, preferrably before implementing index.

Rule 2:

 If an index has existed for >6 months, verify that it is still required.  I've found that many reports and queries that required indexes cease to  be used after a few months due to ongoing changing requirements.

Rule 3:

 If you think an index is required for performance check whether  rewriting the SQL statement will provide performance benefits first.

Rule 4:

 Understand the application ... why is the table being used? how many  users will use the table? how and how often is the column updated? How  will the index imact the whole environment. Another way of looking at  this is "by making these users happy, which other will you p~$$ off?

Rule 5:

 Understand there are many kinds of indexes. Understand the benefits of  each of these before even thinking of implementing indexes. Finally,  understand that the Rule Based Optimizer does not recognize 'most' of  these additional kinds of indexes, so you MUST be into the Cost Based  Optimizer, and you need to be aware of the implications of that.

Rule 6:

 Regardless of Oracle version, read Thomas Kyte's book "Effective Oracle  by Design" and Jonathan Lewis' book "Practical Oracle8i" before  proceeding. If you've read them, read them again. (I need to reread  them at least every 3 months as there is too much info to retain. Every  rereading increases my understanding significantly!)

You wanted Rules of Thumb. Remember that Rule of Thumb abbreviates to ROT which is a perfect way of understanding the value of a Rule of Thumb. AFAICT, most Oracle myths started out as ROT and many are now ROTten.

hth/FGB Received on Thu Feb 17 2005 - 06:17:23 CST