Re: Oracle's Myth: keep tables and indexes in separate tablespaces

"Volker Hetzer" <volker.hetzer_at_ieee.org> wrote in message news:bm6l3t$fdg$1_at_news.fujitsu-siemens.com...
>
> "Richard Foote" <richard.foote_at_bigpond.com> schrieb im Newsbeitrag
news:Wczhb.145191$bo1.144265_at_news-server.bigpond.net.au...
> > There is no doubt that Oracle itself is the biggest myth spreader of
them
> > all. Just look at it's own OTN site where you can still read why
> > indexes/tablespaces should be split for performance, that Oracle indexes
> > become unbalanced over time and need frequent rebuilding
> They don't become unbalanced???
> Could you tell me more about it?
> Because the Documentation on my 9i CD says
> "Serializing within Indexes
> Use of sequences, or timestamps, to generate key values that are indexed
> themselves can lead to database hotspot problems, which affect response
time and
> throughput. This is usually the result of a monotonically growing key that
results in
> a right-growing index. To avoid this problem, try to generate keys that
insert over
> the full range of the index. This results in a well-balanced index that is
more
> scalable and space efficient. You can achieve this by using a reverse key
index or
> using a cycling sequence to prefix and sequence values."
>
> So, presumably there are unbalanced indexes too. I mean, I can accept that
there are all
> sorts of docs on the otn but I always assumed that the delivered doc
always has the final
> word.
>
> Lots of Greetings!
> Volker

Volker,

It kinda depends on which way people define unbalanced. unbalanced to some means vertical imbalance
and to some it means horizontal imbalance. By horizontal imbalance (which might not be a word though :), its meant that indexes might have more
entries in some leaf blocks and less in other.

Jonathan explains this very clearly in one of his papers: http://www.dbazine.com/jlewis13.shtml

Anurag Received on Fri Oct 10 2003 - 11:45:02 CDT