Archive for April, 2014

What is Reverse Key Index in Oracle?

April 19, 2014 Comments off

Reverse key indexes, as their name involves, robotically reverse the order of the bytes in the key value contained in the B-tree index. If the value in a row is ‘ABCD’, the value for the reverse key index for that row is ‘DCBA’.

To comprehend require for a reverse key index, you have to examination some basic truths about the standard B-tree index. First and primary, the depth of the B-tree is decided by the number of entries in the leaf block nodes. The greater the depth of the B-tree, the more levels of branch nodes there and the more I/O is required to locate and access the appropriate leaf block node.

reverse key indexThe new B-tree index is balanced, with an even allocation of entries across the size of the leaf pages. But some values commonly used for an index are not so well behaved. Incremental values, such as ascending sequence numbers or progressively later date values, are always inserted to the right side of the index, which is the home of higher and higher values. In addition, any deletions from the index have a tendency to be skewed toward the left side as older rows are deleted. The net effect of these practices is that over time the index turns into an unbalanced B-tree structure, where the left side of the index is more meagerly populated than the leaf nodes on the right side. This unbalanced growth has to overall effect of increasing the depth of the B-tree arrangement due to the number of entries on the right side of the index. This results described here also apply to the values that are automatically decremented, except that the left side of the B-tree will end up holding more entries.

You can resolve this issue by occasionally dropping and recreating the index or rebuild index. However, you can also resolve it by using the reverse value index, which reverse the order of the value of the index. This reversal causes the index entries to be more evenly distributed over the width of the leaf nodes. For example, rather than having the values 824,825, and 826 be added to the maximum side of the index, they are translated to the value 428, 528, and 628 for storage and then translated back when the values are regained. These values are more consistently spread throughout the leaf nodes.

The generally result of the reverse index is to correct the inequality caused by repeatedly adding increasing values to a standard B-tree index. Performance point of view, reverse key index is the best solution for gaining performance of increasing number, sequence like unique values and duplicate values both.

I found the best resource to get all Oracle DBA interview Questions. Off course, there is best book available at Oracle DBA Interview Questions for real time scenario based questions, answers, and explanations.