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

On Apr 20, 10:33 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Shakespeare wrote:
> > "DA Morgan" <damor..._at_psoug.org> schreef in bericht
> >news:1208624947.696580_at_bubbleator.drizzle.com...
> >> 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
> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Group
> >>www.psoug.org
>
> > 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.

Indeed!

> 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
> using DBMS_STATS.SET_TABLE_STATS and SET_INDEX_STATS how Oracle was
> 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