Reverse Index ... Advantages
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Reverse Index ... Advantages

  1. #1
    Join Date
    Feb 2001
    Posts
    184

    Red face

    Hi,

    Does any one know what is the benifit of having Reverse Index and how it is used.

    Suppose a reverse index is created on Empno, What is the advantage of this instead of the Reguler one.

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    It helps reduce I/O bottlenecks and index sliding. Here is a link:

    [url]http://www.oracle.com/pls/use/use_asset_html.download_asset_form?[/url]

    HTH,

    - Chris

  3. #3
    Join Date
    Feb 2001
    Posts
    184
    Hi Chris,

    Did you see that Link byr the Way.

    I did not get any thing except some parameters ... That's it.

    Thanks any way.

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Doh!

    Sorry about that, but I can't seem to get a better link for this one. Best I can tell you is go to OTN and search on 'Indexing Strategies', which will point you right to the doc. You can also search for 'reverse key' or 'index sliding' to get lots more info on the subject.


    Basically, reversing the key allows the data to be evenly distributed across the entire index, rather than always adding the data at the end - which is only true or even a concern if you are indexing a current-tyoe DATE field or a field populated via a SEQUENCE (or similar). In such cases, you are always adding to the end of the index, which means the same leaf block, and can cause I/O contention, especially in parallel server, but also in non-parallel mode. Further, such inserting will eventually cause the index to 'slide', or become longer on that side, since that is where all the activity and page splitting is occurring.

    The drawback is that you cannot do a ranged-select on a reversed-key index. Well, *you* can, but the optimizer cannot :). You will obviously get the correct answer, just not nearly as quickly. This is not an issue (or should not be) with a sequence-populated key, since this usually is a PK, but can definitely be a concern when dealing with dates.

    HTH,

    - Chris

  5. #5
    Join Date
    Feb 2001
    Posts
    295
    Try [url]http://oradoc.photo.net/ora816/server.816/a76965/c08schem.htm#3239[/url]


    It gives info about index types, their use and their advantages.


    Adriano.

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