Re: HowTo find out used (useful) and unused (usesless) indexes?

From: Robert Klemme <>
Date: Mon, 21 Apr 2008 01:24:52 -0700 (PDT)
Message-ID: <>

On Apr 20, 10:33 pm, DA Morgan <> wrote:
> Shakespeare wrote:
> > "DA Morgan" <> schreef in bericht
> >
> >> Shakespeare wrote:
> >>> But an index may become useful over time, true?
> >>> Shakespeare
> >> On that basis alone one could justify putting an index on every
> >> column of every table so I will respectfully disagree unless you
> >> write a very broad definition of "may."
> >> You need to understand your data and how it is being accessed.
> >> The extra overhead of an unused index is not value added.
> >> My recommendation would be to use the DBMS_STATS.SET.... procedures
> >> to see how queries will react to the expected future growth of both
> >> tables and indexes.
> >> --
> >> Daniel A. Morgan
> >> Oracle Ace Director & Instructor
> >> University of Washington
> >> (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >>
> > I was aiming at data with for example a 'year' column. This column could be
> > indexed by design, but in the first year (all records same value) this index
> > is not useful and won't be used. But on the first entry in the second year
> > it is useful to find entries of that year and so on. A script to remove or
> > disable unused indexes would remove/disable this index in the first year.
> > Shakespeare
> Interesting example.


> First year the index is worthless. Second year it is valuable for some
> short amount of time and then, again, may become useless as the
> percentage of records in each of the two years approaches 50%. Go for
> the third year ... again possibly usable for some period of time and
> then all three years probably tend toward 33.3%. By the end of year
> four you are going for 25%.

That's kind of oscillating usability until it levels out (after few years). That's an interesting way to look at it - very insightful. Usually I considered an index to become useful after a certain amount of data and never change back to unusable - but there is an exception to every rule. Thanks for the education!

> I would not be all that ready to build that index unless I know, again
> going to use it. That is not to say the index might not make a lot of
> sense ... I just would insist on testing any assumption before
> deciding to either build it or not.

... and consider alternatives. This example has written "partitioning" all over it.

Kind regards

robert Received on Mon Apr 21 2008 - 03:24:52 CDT

Original text of this message