Removing Descending Indices for PeopleSoft Databases (Doc ID 1909646.1)

Last updated on AUGUST 05, 2024

Applies to:

Purpose

From PeopleTools 8.48 through 8.53, index columns designated in Application Designer as descending were built with a DESC clause when the index was built in Oracle.  This feature has resulted in overhead that over time has proven unnecessary and in some cases detrimental to performance. 

The PeopleTools strategy regarding the use of descending indexes has changed to now eliminate the use of DESC clause in Oracle 12c.  This approach is required for implementations on Oracle 12c and is optional, appropriate, and supported for existing Oracle 10g and 11g implementations.

The use of the DESC clause was helpful for sorting operations, but it increased the complexity of using the indexes for forward scanning operations, which are very common in PeopleSoft applications.  The result is an overall degradation in the efficiency of the indexes.

The document will demonstrate how both new and existing PS install customers can disable this feature.

As is the case when making a broad change to a system such as the ones described in this note, it is necessary to perform adequate testing and tuning to ensure system stability and performance.

Scope

PeopleTools 8.48 and 8.53 customers using Oracle 10g through 12c. 

Note: Starting with PeopleTools 8.54, PeopleTools will no longer generate the DESC clause for indexes on the Oracle platform.

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.