Reverse key indexes -- REQUEST_ID in FND_CONCURRENT_REQUESTS -- was RE: Table growth - disk sizing

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 26 Aug 2005 19:49:50 +0800

I have frequently wondered if REQUEST_ID in the Oracle Applications table FND_CONCURRENT_REQUESTS
would be a good candidate to index using a Reverse Key index. There are certain peak times in my
organisation's ERP processing when 3500 requests may be submitted within the space of 30 to 50 minutes
{Event Alert Triggers}.
This column is a Unique Key (could actually have been defined as a Primary Key) and is queried by single
values, not by range scans. {Other queries on the table may be executing Range Scans on the
REQUESTED_START_DATE or ACTUAL_START_DATE (for daily "Performance of Concurrent Requests" reports)
or USER_ID columns}

Should I try rebuilding FND_CONCURRENT_REQUESTS_U1 on REQUEST_ID as a Reverse Key Index ?
Opinions ?


At 03:51 AM Friday, Christian Antognini wrote:

In a "regular" index the data is stored in ascending order (e.g. ..., 11, 12, 13, 14, 15, 16, ...). If you create an index on a column where the data is inserted in "progression" (e.g. timestamp of the transaction or value generated by a sequence), it's very likely, for concurrent transactions, to modify the right-most leaf block. Of course this situation will result in waits.

Now, if you store the data in descending order (e.g. ..., 16, 15, 14, 13, 12, 11, ...) you will simply move the contention to the left-most leaf block, i.e. it's not reduced.

A possible solution for such a contention problem, it's to store the data in reserve order (e.g. ..., 11, 21, 31, 41, 51, 61, ...). With this method the transaction are well spread over "all" leaf blocks. Of course they are drawbacks as well. One of the most important is the poor support of range scans (the data is in the wrong order...).


New Features Oracle Database 10g Release 2 seminars @
Italiano: Lugano (24-Nov)
Français: Genève (17-Nov)
Deutsch: Zürich (11-Oct), Hamburg (13-Oct), München (20-Oct), Basel (25-Oct),
Frankfurt (27-Oct), Bern (8-Nov), Düsseldorf (23-Nov), Stuttgart (13-Dec)

Hemant K Chitale


Other related posts: