Craig Shallahamer's Blog
|
You were brought to this page based on an internet search
and as a free service to Oracle DBAs.
The text below is an except from the book,
Oracle Performance Firefighting, written by
Craig Shallahamer of
OraPub, Inc.
Figures and tables are not included on this page, only their reference.
To order the book in either print or PDF form, click
here.
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
-------------------------------
<p>Here's an example of how Oracle reverses the bytes of each indexed column. Suppose the sequence numbers are represented in 4 bytes. So the first four sequences (1, 2, 3, 4) would be represented as 0001, 0010, 0011, and 0100. If these four values were used in the index, because index ordering must be maintained, they will be placed next to each other. However, if they bytes were reversed, they would be represented like this: 1000, 0100, 1100, and 0010. Since they must also be placed into the index in sorted order, they will likely not be placed into the same index leaf block. In fact, they will probably span all the index leaf blocks. As a result of key reversal, the buffer busy wait will be eliminated.
</p><p>Creating a reverse key index is very simple. Use the same index creation DDL as usual, but as the following code snippet shows, simply add the reverse keyword at the end. That's it!
</p><p>One final note: Because there will be intense index insertion in every leaf block, to reduce the frequency of leaf block splitting, consider adding the pctfree 50 storage parameter when creating reverse key indexes.
</p>
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
|
|
Know what's important before it's too late!
OraPub's
Performance Training
is like no other...
More Class Pics...
Get student testimonials!
|
