Re: Revese Key Index

Generally no; but there are special cases.

1. If you are running Oracle parallel server and inserting from more than one node fairly frequently
2. If you are not running Oracle parallel server, but have several processes inserting single rows concurrently at a VERY high rate - even then you may survive by setting INITRANS on the index to a higher value than default, making sure that the CACHE on the sequence is high, and creating multiple freelists on the table.

The drawback to reverse key on numeric sequences is that the stored value is meaningless garbage, so can really only be used for parent/child links.

There are other, application dependent, side-effects to consider as well. The main trade-off of the reverse key is the potential for reduced contention on the trailing leaf block on inserts vs. the potential increased random I/O on retrievals.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Paul Bennett wrote in message <387104D4.B16FB6FE_at_cc.gatech.edu>...
>If someone is using an auto increment primary key, is it a good idea to
>create a reverse index on this column?
>
>Are there any disadvantages to creating a reverse index? What if a DBA
>just rebuilds a normal index periodically. Would that take away the
>advantage of a reverse key index?
>
>Thanks.
>
>-- Paul
>
>John Shaft wrote:
>
>> Hi,
>>
>> Does Oracle has a way to define a table such that the primary key gets
>> auto-incremented with each new insertion? If not, how do you keep your
>> primary keys unique? (With a store value read and incremented in a
>> transaction?)
>>
>> I have a databse devloped orignally for MySQL and want to port it to
>> Oracle 8. I wondered about this issue.
>>
>> Thanks!
>>
>> shaft_at_meanmutha.com
>> http://www.meanmutha.com
>
Received on Mon Jan 03 2000 - 15:17:52 CST