Re: Reverse order indexes in Oracle8

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 Received on Thu Apr 08 1999 - 23:26:36 CDT