Advantage of Reverse Index..
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Advantage of Reverse Index..

  1. #1
    Join Date
    Feb 2001
    Posts
    184
    Hi,

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

    Thanks..

  2. #2
    Join Date
    Apr 2001
    Posts
    37
    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.
    inosov
    Brainbench MVP for Oracle DBA

  3. #3
    Join Date
    Feb 2001
    Posts
    184
    Thanks man.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Feb 2001
    Posts
    389
    Also they use more space.
    Also they have a bug on creating on a foreign key.

  6. #6
    Join Date
    Apr 2001
    Posts
    257
    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,


  7. #7
    Join Date
    Apr 2001
    Posts
    37
    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.)
    inosov
    Brainbench MVP for Oracle DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width