Re: How many is too many

On 11 August 2011 22:50, Kellyn Pot'vin <kellyn.potvin_at_ymail.com> wrote:

> I have to add this a bit to this....
>
> Every index that is requested should be "justified".
> What this translates to:
> -Proof that the index is actually used must be produced, (or will be
> used...) I don't know how many times I've been given DDL for an index
> someone thought their query would use and you pull a simple explain plan and
> find out otherwise.
> -The cost to support the index should be calculated- "What will it cost to
> updates, inserts and deletes that are performed against this object in
> relation to the savings to have this index in place to the business?"
> -If it's a partitioned object, what kind of index are you requesting and if
> it's a global index, you had better be sacrificing a small animal to prove
> to me the overhead in management to my team is worthy of it... :)
>
> Reviewing and auditing index usage in a mature production environment is
> rarely a waste of time. The task once allowed me to drop 30% of the indexes
> in an environment and re-allocate over 1TB in space back to the system.
>
> Kellyn Pot'Vin
> Sr. Database Administrator and Developer
> dbakevlar.com
>
> ------------------------------
> *From:* "Lange, Kevin G" <kevin.lange_at_ppoone.com>
> *To:* ORACLE-L <oracle-l_at_freelists.org>
> *Sent:* Thursday, August 11, 2011 3:22 PM
> *Subject:* RE: How many is too many
>
> That is a perfectly stated response. Too many is when adding them starts
> to hurt performance. I will not tell you how many one of our tables has
> because you would call me nuts.
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Morten Egan
> *Sent:* Thursday, August 11, 2011 4:18 PM
> *To:* veeeraman_at_gmail.com
> *Cc:* ORACLE-L
> *Subject:* Re: How many is too many
>
> The number that is considered a no-no, is the number where things start to
> go unacceptably slow :)
>
> Joking aside, if your program does what it is supposed to do, in an
> acceptable time, then it really doesn't matter.
>
> 2011/8/11 Ram Raman <veeeraman_at_gmail.com>
>
> Listers,
>
> I am looking at a table in our system and it has 12 indexes, we are
> planning on adding another one. I am aware of the effects of having too many
> indexes, but in this case adding an extra index helps a certain query that
> runs slow. Other queries and most other operations against the table
> are acceptable too. I see a few tables like this; is there a number above
> which is considered a no-no when it comes to adding more indexes.
>
> PS. The tables and queries are structured in a way that seem to require
> several indexes - it is a third party product.
>
> TIA,
> Ram.
>
>
>
>
> --
> Regards,
> Morten Egan
> http://www.dbping.com
>
>
> This e-mail, including attachments, may include confidential and/or
> proprietary information, and may be used only by the person or entity
> to which it is addressed. If the reader of this e-mail is not the intended
> recipient or his or her authorized agent, the reader is hereby notified
> that any dissemination, distribution or copying of this e-mail is
> prohibited. If you have received this e-mail in error, please notify the
> sender by replying to this message and delete this e-mail immediately.
>
>
>

-- 
Howard A. Latham

Sent from my Nokia N97

--
http://www.freelists.org/webpage/oracle-l