Reverse index: Difference between revisions

[[Database management systems]] provide multiple types of [[index (database)|index]]es to improve performance and data integrity across diverse applicationapplications. Index types include [[b-tree]]s, [[Bitmap index|bitmaps]], and [[r-tree]]s.

In database management systems, a '''reverse key index''' strategy reverses the [[Unique key|key]] value before entering it in the [[Index (database)|index]].<ref>http{{Cite web|url=https://richardfoote.wordpress.com/2008/01/14/introduction-to-reverse-key-indexes-part-i/|title=Introduction To Reverse Key Indexes: Part I|date=2008-01-14|website=Richard Foote's Oracle Blog|language=en|access-date=2019-04-13}}</ref> E.g., the value 24538 becomes 83542 in the index. Reversing the key value is particularly useful for indexing data such as [[surrogate key|sequence number]]s, where each new key value is greater than the prior value, i.e., values monotonically increase. Reverse key indexes have become particularly important in high volume [[transaction processing system]]s because they reduce [[Block contention|contention]] for index [[Block (data storage)|blocks]].

Reversed key indexes use [[b-tree]] structures, but preprocess key values before inserting them. Simplifying, b-trees place similar values on a single index block, e.g., storing 24538 on the same block as 24539. This makes them efficient both for looking up a specific value and for finding values within a range. However, if the application inserts values in sequence, each insert must have access to the newest block in the index in order to add the new value. If many users attempt to insert at the same time, they all must write to that block and have to get in line, slowing down the application. This is particularly a problem in [[Cluster (computing)|clustered databases]], which may require the block to be copied from one computer's memory to another's to allow the next user to perform their insert.

Reversing the key spreads similar new values across the entire index instead of concentrating them in any one leaf block. This means that 24538 appears on the same block as 14538 while 24539 goes to a different block, eliminating this cause of [[Shared resource|contention]]. (Since 14538 would have been created long before 24538, their inserts don't interfere with each other.)and it is very useful.

Typically, applications delete data that is older on average before deleting newer data. Thus, data with lower sequence numbers generally go before those with higher values. As time passes, in standard [[b-tree]]s, index blocks for lower values end up containing few values, with a commensurate increase in unused space, referred to as "rot". Rot not only wastes space, but slows query speeds, because a smaller fraction of a rotten index's blocks fit in memory at any one time. In a b-tree, if 14538 gets deleted, its index space remains empty. {{Citation needed span|text=In a reverse index, if 14538 goes before 24538 arrives, 24538 can reuse 14538's space.|date=March 2022}}

* [[reverseReverse dictionary]]

*http{{Cite web|url=https://downloaddocs.oracle.com/docs/cd/A87860_01/doc/paraserv.817/a76970/design.htm|title=Database Design Techniques|website=docs.oracle.com|access-date=2019-04-13}}