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

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 ?

Hemant

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...).
>
>HTH
>Chris
>
>
>New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com
>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)
>--
>http://www.freelists.org/webpage/oracle-l

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

--
http://www.freelists.org/webpage/oracle-l