Re: How many is too many

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. 

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