Re: How many is too many
- From: Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx>
- To: "kevin.lange@xxxxxxxxxx" <kevin.lange@xxxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 11 Aug 2011 14:50:57 -0700 (PDT)
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@xxxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Morten
Egan
Sent: Thursday, August 11, 2011 4:18 PM
To: veeeraman@xxxxxxxxx
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@xxxxxxxxx>
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.
Other related posts:
