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>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><p>To give you an idea of the performance impact of using a reverse key index, I created the test situation I described earlier (in "The Situation" section). Over the 120-second test interval, using a nonreverse index, 75% of all wait time was related to buffer busy waits events on an index block with a transaction rate of 0.80 trx/sec. (Each transaction inserted 50,000 rows.) I reran the test with the only difference being the creation of a reverse key index. Over the 120-second test interval, 28% of the wait time was related to buffer busy waits events, with a transaction rate of 0.95 trx/sec. That's great news, because the wait time decreased and the transaction rate increased!
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!
|
