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

"Richard Foote" <richard.foote_at_bigpond.com> schrieb im Newsbeitrag news:QPThb.146517$bo1.128474_at_news-server.bigpond.net.au...
> "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?
>
> Hi Volker,
>
> This has been done to death recently but (deep breath) here goes ...
>
> Oracle indexes are always balanced, always. By that I mean all leaf nodes
> are on the same level within the B-Tree structure, that's all leaf nodes,
> exactly the same level or "distance" from the root node. Always.
All right, my dba teather told the exact opposite and taught us to check the depth of indexes. That's why I was confused.

> Indexes can become "skewed" or have blocks less densely populated in some
> parts of the index than in other parts. This is what some consider to be
> "unbalanced"

All right, but if we are talking B-trees, the meaning of "unbalanced" is generally well defined, meaning different leaf nodes have different distances to the root.

> Lets examine the text you quote carefully.
>

[reason fir right-growing indexes=bad: contention for the last buffer if multiple inserts with monotonous growing sequences. Solution:]
> >To avoid this problem, try to generate keys that insert over
> > the full range of the index.

> Firstly, there is a misconception that by always inserting into the
> "right-most" leaf node, you somehow either make the index unbalanced (for
> those who have absolutely no idea how indexes work) or skewed (for those who
> only have a vague understanding how indexes work). It all comes down to what
> happens when the last leaf node gets full.
My teacher told us indexes don't get rebalanced (as university tought b-trees get) which I found hard to believe anyway.

[you state that indexes get rebalanced]
[You say that indexes get rebalanced when deleting too] Ok, what you say makes sense to me because I believe the overhead of balancing to be noth a prohibitive burden. When I've got some time I'll test this.

> "Never Assume" !!

Thanks a lot!!

Volker Received on Mon Oct 13 2003 - 07:05:56 CDT