about 12 is too many. Lathams law On 11 August 2011 22:50, Kellyn Pot'vin <kellyn.potvin@xxxxxxxxx> 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@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. > > > -- Howard A. Latham Sent from my Nokia N97