RE: How many is too many
Date: Fri, 12 Aug 2011 05:43:47 -0700
Message-ID: <D29F9902E534D5478F2E83FD6A44B3063B038A7B39_at_mail02.mba.xifin.com>
Why not monitor the indexes to see if they are being used?
Sometimes indexes are created for a query that is only executed once in a blue moon.
If indexes are not used in 1,2,3,5 months, then drop them? You need to determine the duration?
For indexes not used, mark them as invisible first. If no one screams, then drop them.
Indexes are labeled with PK, UK, FK do not get dropped.
When troubleshooting performance issues and I have to add an index, I use $ versus _ to indicate that it was added by me for this reason, i.e. IX$TAB$COL versus IX_TAB_COL
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn [greg_at_structureddata.org] Sent: Thursday, August 11, 2011 10:47 PM To: oracle-l_at_freelists.org
Subject: Re: How many is too many
For OLTP, adding indexes is a known quantity (in general). There is a known and finite number of queries so adding indexes is about limiting the number of blocks visited.
For DW, adding indexes in this manner (12 indexes on a table, etc.), is the beginning of the end. Data loads suffer and since query workloads are frequently unknown (ad-hoc), DBAs often misapply OLTP techniques - adding indexes for each problem query they see. Seems people forget why databases have indexes -- as a manner to efficiently access a small number of rows. When indexes are used as a means to reduce IO, it is then being mis-applied because the platform is not appropriately sized and/or correct DW technologies are not being applied (e.g. parallel execution + table scans) for a large number of rows problem.
In any case, one has to start asking what good reasoning there is for 12 indexes. That just shouts bad design and/or inappropriate tuning methodologies. Start asking why 5 times<http://en.wikipedia.org/wiki/5_Whys>.
On Thu, Aug 11, 2011 at 8:14 PM, Ram Raman <veeeraman_at_gmail.com<mailto:veeeraman_at_gmail.com>> wrote:
Rule of thumb! Thanks for the response Greg. Does this apply to Data warehouse or OLTP applications or both?
On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn <greg_at_structureddata.org<mailto:greg_at_structureddata.org>> wrote: Sounds like the "index death spiral" to me. My simple sanity check: when the total number of columns indexed is greater than the number of columns in the table, there is likely a design or tuning problem. Often times, even before then...
--
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Aug 12 2011 - 07:43:47 CDT