RE: How many is too many
- From: Michael Dinh <mdinh@xxxxxxxxx>
- To: "greg@xxxxxxxxxxxxxxxxxx" <greg@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 12 Aug 2011 05:43:47 -0700
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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Greg Rahn [greg@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, August 11, 2011 10:47 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx<mailto:veeeraman@xxxxxxxxx>> 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@xxxxxxxxxxxxxxxxxx<mailto:greg@xxxxxxxxxxxxxxxxxx>> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
