Re: separate data/inidex

Nuno Souto wrote:

> In article <aam0i3091q_at_drn.newsguy.com>, you said (and I quote):
> > tons of them think the most discriminating field needs to go first in an index
> > and so on....
>
> this is an interesting one. I can't for the life of me remember when
> this was ever true (as in which version)? I've always measured it not to
> be so in ALL versions of Oracle I worked with, since V4. Did I miss a
> long series or is this another big myth from the start? It just doesn't
> make sense at all to do it this way when one reads how concat indexes are
> processed and stored.
>
> > > If they find it help performance
> > >they keep doing it. Otherwise they stop.
> >
> > I find NEITHER to be true.
> >
> > A) they never measure it
> > B) they *never* stop.
>
> too true.
> unfortunately a lot of them do a lot by rote, rather than by need. number
> of times I've been crucified at sites for saying that defrag to 1 extent
> is a waste of time is not even fun! Since V6 it hasn't really been
> needed. All we had to do back then was set the right values for
> dc_used_extents and dc_free_extents and bingo: all objects could live
> with a (reasonable!) number of extents without any impact on performance.
> Problem was no-one ever changed the default values of these two, which
> were ridiculously small. Hence the whole myth started. And kept going
> all the way through 7.0, 7.1, etc etc, where these parameters were auto-
> set by Oracle to huge values and the problem disappeared.
>
> --
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam

It was not entirely myth. I had an engagement about five years ago, it was a finance system (I think it 8.0.5 on NT 4 w/ 1GB RAM), where deletes were taking substantial amounts of time (10-15 minutes). I decreased the number of extents from tens of thousands to a few dozen and deletes took only seconds.

Sometimes myths have a basis in fact though they may be misapplied.

Daniel Morgan Received on Tue Apr 30 2002 - 10:44:24 CDT