Shakespeare wrote:
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... (AT) psoug (DOT) org> wrote:
Indeed!

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!

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

Kind regards

robert

On Apr 20, 10:33 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Indeed!

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!

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

Kind regards

robert

On Apr 20, 10:33 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Indeed!

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!

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

Kind regards

robert

On Apr 20, 10:33 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:
Indeed!

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!

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

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

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

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... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
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.

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