Re: Reverse order indexes in Oracle8

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Fri, 09 Apr 1999 08:33:41 -0700
Message-ID: <370E1DD4.D708BD05@us.oracle.com>

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.

The other use for the reverse key index is in Parallel Server, but that's probably outside the scope of this discussion.

HTH. Pete

Jonathan Gennick wrote:

> On Thu, 08 Apr 1999 18:30:36 GMT, dejanews_at_datagen.com
> wrote:
>
> >"... you can create indexes that reverse the order of the data prior to
> >storing it. That is, an entry whose data value is '1002' will be indexed as
> >'2001'. The reversing of the data prior to indexing helps keep the data
> >sorted better within the index. ..."
> >
> >Does anyone know why reversing of the data helps keep that data sorted better?
>
> It probably depends on your a data. The examples I've seen
> are all based around an index on an incrementing value. Say
> that you are inserting rows with the following unique values
> for the primary key:
>
> 100001
> 100002
> 100003
> 100004
> ...
>
> The leading character will be "1" for quite some time. I
> think that can lead to hot spots in the index, where all the
> users are competing to update the same index nodes.
> Reversing the index gives you values like:
>
> 100001
> 200001
> 300001
> 400001
>
> These are much more spread out in terms of their leading
> character. You could probably expect an even distribution
> over the digits "0" through "9", and that might help spread
> out the i/o a bit.
>
> I wonder though, if reversing the index would have a
> negative effect on a query that was trying to retrieve all
> entries that started with a "1". I think it would.
>
> Jonathan

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 Fri Apr 09 1999 - 10:33:41 CDT