On Apr 20, 10:33 pm, DA Morgan <damor...@[EMAIL PROTECTED]
> wrote:
> Shakespeare wrote:
> > "DA Morgan" <damor...@[EMAIL PROTECTED]
> schreef in bericht
> >news:1208624947.696580@[EMAIL PROTECTED]
> >> 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 Wa****ngton
> >> damor...@[EMAIL PROTECTED]
(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
|
