Re: How many is too many

  • From: David Fitzjarrell <oratune@xxxxxxxxx>
  • To: "mdinh@xxxxxxxxx" <mdinh@xxxxxxxxx>, "greg@xxxxxxxxxxxxxxxxxx" <greg@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 12 Aug 2011 07:58:56 -0700 (PDT)

I've found a few cases over the years where an index wasn't 'used' (as in 
accessed) but its statistics WERE used by the optimizer and when the index was 
dropped the pl;ans changed for the worse (it's not common but it CAN happen):

David Fitzjarrell

From: Michael Dinh <mdinh@xxxxxxxxx>
To: "greg@xxxxxxxxxxxxxxxxxx" <greg@xxxxxxxxxxxxxxxxxx>; 
"oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, August 12, 2011 5:43 AM
Subject: RE: How many is too many

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 

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<>.

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...

Greg Rahn

Other related posts: