Re: Reverse order indexes in Oracle8

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Thu, 15 Apr 1999 11:30:22 -0700
Message-ID: <3716303E.840A94BC@us.oracle.com>

Sergey

The easiest answer is diagrammatic, but it may be problematic to format. Think of it this way. If we have a monotonically increasing sequence number that's indexed, what goes into the index? Let's use an example of an sequence starting with 1, where the index is created immediately after the sequence. As the sequence increases, the values 1, 2, 3, 4, ... are stored in the index block (simplistic view. but it will do for the explanation). Let's say we can only store four values in our index block. So our root node of the B-tree looks like this:

1,2,3,4

Now what happens when we want to fit 5 (the next sequence value). The root node will split to the right, so we end up with a structure that looks like this:

1, 2, 3, 4

(Draw the line between the blocks yourself). Now this block starts filling, and when we reach 9, we need to split again:

1, 2, 3, 4

                 5, 6, 7, 8

                                   9, ...

Diagrammatically, the B-tree is splitting to the right.

For those perfectionists out there, yes I know there's more to it than this, but this is a simplistic attempt to explain a more complex concept.

HTH. Pete

dejanews_at_datagen.com wrote:

> In article <370E1DD4.D708BD05_at_us.oracle.com>,
> Pete Sharman <psharman_at_us.oracle.com> wrote:
> >
> > Both the answers to this question left out one vital part. Reverse-key
> indexes
> > work the way stated in earlier responses, but the problem occurs when a
> > monotonically increasing sequence number index splits to the right AND there
> is
> > little delete activity. The reason the extra bit is important is that if we
> delete
> > all the entries for an index block, there is some rebalancing of the index
> tree
> > that takes place automatically. If there is no delete activity, then we just
> keep
> > splitting the B-tree to the right for normal indexes. In a reverse key index
> the
> > values are changing more randomly if you like to think of it that way, so you
> get a
> > more balanced index.
>
> What exactly is a B-tree split?
>
> Sergey
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Regards

Pete

Peter Sharman                             Email: psharman_at_us.oracle.com
WISE Course Development Manager           Phone: +1.650.607.0109 (int'l)
Worldwide Internal Services Education            (650)607 0109 (local)

San Francisco

SQL> select standard_disclaimer, witty_remark 2 from company_requirements;

Opinions are mine and do not necessarily reflect those of Oracle Corporation

"Controlling application developers is like herding cats." Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!" Bruce Pihlamae, long term ORACLE DBA

Received on Thu Apr 15 1999 - 13:30:22 CDT