Shakespeare wrote:
> "DA Morgan" <damorgan@psoug.org> schreef in bericht
> news:1208624947.696580@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
>> damorgan@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.

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

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.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Apr 20, 10:33 pm, DA Morgan <damor...@psoug.org> wrote:
> Shakespeare wrote:
> > "DA Morgan" <damor...@psoug.org> schreef in bericht
> >news:1208624947.696580@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...@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

Thank all of you,

I know that there can be indexes that are only used very seldom so that
a too short observing distance (this may be days or years) is a bad
idea.

In my case I rebuilt some structure of a database and all the queries
are frequently hit by only one program. But these are too many queries
to watch every so that the idea is to bulid the new needed indexes and
after a period of maybe a month to kill (disable -> drop) all indexes
that are not used.
Maybe not the best but maybe the fastest way to rebuild.

Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

On Apr 18, 4:01*pm, yf...@vtn1.victoria.tc.ca (Malcolm Dew-Jones)
wrote:
> joel garry (joel-ga...@home.com) wrote:
>
> : On Apr 18, 2:29=A0am, Andreas Mosmann <mosm...@expires-30-04-2008.news-: group.org> wrote:
>
> : > Thank both of you,
> : >
> : > I will try it out.
> : > Is there also a way to determine what index is still needed/useful fora
> : > special query?
> : >
> : > Andreas Mosmann
> : >
> : > --
> : > wenn email, dann AndreasMosmann <bei> web <punkt> de
>
> : I do believe that is the downside of deleting indices based on usage.
> : It only shows what's been used during the observation. *That implies a
> : bad assumption that the usage is completely stable. *To me, this seems
> : worse than just dropping an index and seeing who screams, since when
> : there is a problem in the future, you have to go through an entire
> : performance tuning workup because the linkage to the act of dropping
> : the index is obscured. *Maybe I'm missing the concept. *What about an
> : index that would be used when you pass some tipping point or boundary
> : condition or upgrade or change a session parameter?
>
> You can disable an index. *That way the definition exists but the index is
> never used or maintained (i.e. no overhead). *If you decide it is needed
> you simply enable it.

But that's my point. The decision is being made on past performance,
why would you decide it is needed? Certiainlly if you are using
method-r the users would be complaining about it far too late, it
could have been needed for years between the time it was dropped and
the time a complaint is made - people tend to think "that's how the
system works" and not complain if something gets slower slowly. I
still don't see what synapses would have to be fired (thanks for that
one, Dan!) to make this tool useful. In my experience, either the
system has been well-vetted over time (like an enterprise system sold
to many customers), or it's newly developed and the thought have been
tested and decisions made (or, it's just a crappy system). It ought
to be useful for the enterprise, as vendors can't know what parts of
the system the customer will use, but I haven't seen much of that, as
a DBA I've only seen missing indices, and sometimes it takes quite a
bit of work to figure that out. Has anybody actually found this tool
useful? (not a rhetorical question, I'm curious, and always wondering
about it when I see some new feature trumpeted everywhere, but not
success stories. Of course, I don't see everything.).

Since it does take work to figure missing indices out, I can't help
but wonder if this tool is counterproductive, by making it too quick
on the draw to delete indices, making more work later. If you have to
make a big project out of it with dbms_stats, with no one complaining
about performance... in a complicated enterprise system, you may not
know how the future data growth will be impacted by business changes
and software upgrades.

>
> "when you pass some tipping point"
>
> If an index is enabled then presumably it will only be used when the CBO
> decides it is useful for a query.

Again, that's my point. The decision is being made before the CBO can
decide that.

jg
--
@home.com is bogus.
“It was them saying, 'We need to stick our hands up your back and move
your mouth for you.' ” - Robert Bevelacqua, retired Army Green Beret
and former Fox News analyst.
http://www.signonsandiego.com/uniont...s_1n20mil.html