RE: indexing

From: Mark W. Farnham <>
Date: Tue, 19 Feb 2013 14:35:21 -0500
Message-ID: <024801ce0ed8$42a16000$c7e42000$>


If showing them the list of indexes you have on the relevant tables does not quickly lead to cooperation and voluntary rationalization of the strategy, then monitoring to show them whether they have unused indexes and following Tim's outline below is probably the best course. If you doubt your audience's ability to cooperate, leaping directly to monitoring, etc., is the way to go.

If you CAN get them to engage in a logical assessment as well, it also covers the case where each of 2 indexes that are slight winners for different queries (and so show up in monitoring as used), but keeping either one would be a big net win by having one index instead of 2.

It sure is hard to argue AGAINST dropping an index if it does not ever get used and you can show that by monitoring index usage. Do watch out for the "small time window" problem when evaluating the results of monitoring: If you ignore something that only happens once a month or once a quarter, dropping an "unused" index based on monitoring some specific day or week can get you in trouble.


-----Original Message-----
From: [] On Behalf Of Tim Gorman
Sent: Tuesday, February 19, 2013 1:40 PM To:
Subject: Re: indexing

I agree with Jackie. Technical reasoning is useful, but often becomes just one position in a heated technical argument moderated by a disinterested non-technical person, but hard facts win the day and pave the way for one's own position.

Monitor the indexes either by using ALTER INDEX ... MONITORING USAGE command and then querying V$OBJECT_USAGE afterwards, or (if you are licensed for AWR) query the DBA_HIST_SQL_PLAN for OBJECT_NAME IN (/index-name-list/) and view the SQL text captured. The former mechanism (i.e. index monitoring) is designed for identifying unused indexes and is guaranteed accurate (see Tim Hall's excellent post at ""), and the latter suggestion (i.e. mining AWR data) is just a quick 'n' dirty way to detect index usage, but is based on sampled data and is not guaranteed accurate.

Once unused indexes have been empirically proven, then you can document the cost in terms of space (by querying DBA_SEGMENTS) and estimate the relative cost to INSERT, UPDATE, and DELETE performance by constructing a copy of the table and it's indexes and using bogus workload generated within a PL/SQL procedure performing a fixed number of INSERT, UPDATE, and DELETE operations against the copied table. By running the PL/SQL procedure as a baseline, then removing the unused indexes and re-running the PL/SQL procedure, you should be able to provide a good estimate of the impact of the useless and unused indexes on transactions.

It would be ideal if the impact in terms of space and transactional processing was impressive, but if it is a small and static table, be prepared for relatively unimpressive justification. In this situation, be prepared for a negative decision to your proposal, but at least you'll have the techiques in place for the same situation involving a large and volatile table.

Hope this helps...

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => PO Box 352151, Westminster CO 80035-2151 USA
web/blog   =>
email      =>
mobile     => +1 (303) 885-4526
twitter    => timothyjgorman

president  -> Rocky Mtn Oracle Users Group (
board      -> Oracle Developers Tools Users Group (
advisor    -> Northern California Oracle Users Group (
secretary  -> Project SafeGuard (
member     -> OakTable Network (

Oracle ACE Director (

Lost Data? => for info about DUDE...

On 2/19/2013 9:34 AM, Jackie Brock wrote:

> Why not monitor indexes for usage? :)
> -----Original Message-----
> From:
> [] On Behalf Of Taral Desai
> Sent: Tuesday, February 19, 2013 9:20 AM
> To: Brian Zelli
> Cc: oracle-l (
> Subject: Re: indexing
> Well. this might be too much but still depends. You need to provide some
more information Like 1. How many approximately rows this table has 2. No. of columns 3. How frequently int's updated/deleted or data is inserted.
> 4. What kind of indexes it has and what are the layout
> And most importantly is you need to ask Is this index is going to help
some query if so then can it be combined with other index.
> So, it's not easy to answer this question without any information.
> On Tue, Feb 19, 2013 at 10:08 AM, Zelli, Brian
> <>wrote:
>> I have developers who are able to create indexes thru their
>> application builder. On one table they have 30 indexes. I've often
>> said to them that they are creating too many but I get overruled by their
manager. Are they
>> creating too many? Can oracle handle many, many indexes? Is there a
>> I can at least minimize any depreciation of performance if they are
>> going to be allowed to do this?
>> ciao,
>> Brian
>> This email message may contain legally privileged and/or confidential
>> information. If you are not the intended recipient(s), or the
>> employee or agent responsible for the delivery of this message to the
>> intended recipient(s), you are hereby notified that any disclosure,
>> copying, distribution, or use of this email message is prohibited.
>> If you have received this message in error, please notify the sender
>> immediately by e-mail and delete this email message from your computer.
Thank you.
>> --
-- --
Received on Tue Feb 19 2013 - 20:35:21 CET

Original text of this message