Re: Do reverse key indexes rebuild themselves???

<alistair.thomson_at_sphinxcst.co.uk> wrote in message news:99vm58$cos$1_at_news.netmar.com...
> Hi
>
> Environment:
>
> Oracle 8.1.6.0.0 on NT4
>
> I've been asked a question regarding reverse key indexes. Specifically,
 does a
> reverse key index perform an automatic index rebuild after a certain
 number
> of entries are made to the index?
>
> My initial reaction was no it doesn't.

You'd be entirely correct on that one. No index automatically rebuilds itself, which many DBAs have come to regret over time!

>
> Also what benefits does a reverse key index bring over a normal btree
 index
> when the column indexed is a primary key field using a sequence to
 generate
> the primary key.
>

The key benefit is that it completely eliminates the enormous contention that can arise when a monotonically incremented sequence number is indexed normally for the last leaf node. Since the sequence number only ever increments, all new index entries would be in the last leaf node, and when you have hundreds or thousands of Users all fighting for the one node, you can expect hideous wait levels. Reversing the index means that inserts are scattered across the entire base of the index, and hence there is no contention for the last node.

The cost, of course, is that it turns the index into behaving like a usual b-tree index: block splits will occur, and the index will get fluffy and inefficient over time, and will thus need rebuilding more frequently than the non-reversed version would do.

Regards
HJR
> Any advice greatly appreciated.
>
> Alistair Thomson
>
>
> ----- Posted via NewsOne.Net: Free (anonymous) Usenet News via the
 eb -----
> http://newsone.net/ -- Free reading and anonymous posting to 60,000+
 groups
> NewsOne.Net prohibits users from posting spam. If this or other posts
> made through NewsOne.Net violate posting guidelines, email
abuse_at_newsone.net Received on Fri Mar 30 2001 - 21:15:20 CST