Advantage of Reverse Index..

Hi,

Can any one please explain the advantage of Reverse key Index and how does it help in utilization of the Index?

Thanks..

A reverse-key index is B*Tree index where the bytes of each indexed column
stored in a reverse order. They are very useful for Oracle Parallel Server
where contention for the same leaf blocks during data inserts can cause
inter-instance "pinging", excessive I/O and performance degradation.

Reversing index keys allows insertions to be distributed evenly across
all the leaf keys in the index.

But reverse-key indexes do not allow index range scan since lexically
adjacent keys are not stored next to each other, so you probably should not
use reverse-key indexes unless you are in OPS environment.

Thanks man.

I would add something to inosov's explanations, which BTW are absolutely correct.

Reverse key indexes are not suitable only for OPS configuration. They can also be very benefitial for normal instances, where rows are frequently added with uniformly increasing values on indexed columns. This typicaly occures with surogate primary keys, generated by sequences. If the transaction rate is very high for such tables, a single index leaf block (holding the latest values generated from sequence) will become a "hot spot", because every inserted row will require an entry in the same leaf block (untill it becomes full, when the next leaf block will become a hot spot). By reversing the bytes in the index entry the load is evenly spread among wider range of leaf blocks.

But be carefull with reverse indexes. As inosov pointed out, they can't be used in range scans. So use them only if you select from this table with equality operators.

Also they use more space.
Also they have a bug on creating on a foreign key.

Hi,

I understand what's been talked about in this thread except:

Inosov said:
"They are very useful for Oracle Parallel Server
where contention for the same leaf blocks during data inserts can cause inter-instance "pinging", excessive I/O and performance degradation."

What is inter-instance pinging? Why does it give excessive I/O?

Thanks,

A inter-instance ping is when one instance requests a data block
which has been modified by another instance but not yet written to disk.

OPS writes or "pings" such block to disk so that the requesting instance can read the most current data.

If all OPS-instances modify the same data blocks, "pinging" becomes very I/O-consuming.
That is why data and application partitioning between instances are so important.

(In OPS 8i, the new feature Cache Fusion directly transfers data blocks among instances
eliminating excessive I/O, but data partitioning is still quite important for OPS performance.)