Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    20

    Question Unanswered: Reverse key indexes

    Hi,

    Can someone explain what reverse key indexes are and why you would use them? Do they have any performance benefits over noreverse key indexes?

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Reverse key indexes

    Originally posted by ajdbn_za
    Hi,

    Can someone explain what reverse key indexes are and why you would use them? Do they have any performance benefits over noreverse key indexes?

    Thanks.
    The purpose of reverse key indexes is to reduce insert "hotspots". Suppose you have a heavily inserted table with an ID coming from a sequence. At any time, several users may be inserting rows with similar sequence numbers like 1043567, 1043568, 1043571, ... In a normal index, these will all get added to the same part of the index (where the highest values are). In a reverse key index, the index entries are reversed, so that they become more random (more like 7654301, 8653401, 1753401, ...) and are therefore inserted all over the index.

    The only possible performance gain is in inserts as above, by avoiding hotspots. On the other hand, performance could be worse if you wanted to do a range scan e.g. WHERE id > 1043500. I think they are only to be used in exceptional circumstances, i.e. where insert hotspots are considered to be a particularly nasty problem.

  3. #3
    Join Date
    Mar 2003
    Posts
    20
    Thanks Tony. That was helpful. :-)

Posting Permissions

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