PeopleTools 8.54: Descending Indexes are not supported

This is the first in a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

"With PeopleTools 8.54, PeopleTools will no longer support descending indexes on the Oracle database platform" - PeopleTools 8.54 Release Notes

They have gone again!  Forgive the trip down memory lane, but I think it is worth reviewing their history.

• Prior to PeopleTools 8.14, if you specified a key or alternate search field in record as descending then where it appeared in automatically generated key and alternate search key indexes, that column would be descending.  PeopleTools would add the DESC keyword after the column in the CREATE INDEX DDL.  Similarly, columns can be specified as descending in user indexes (that is to say ones created by the developer with index ID A through Z).
• In PeopleTools 8.14 to 8.47, descending indexes were not built by Application Designer because of a problem with the descending key indexes in some versions of Oracle 8i.  
• PeopleSoft had previously recommended setting an initialisation parameter on 8i to prevent descending indexes from being created even if the DESC keyword was specified.parameters.

_IGNORE_DESC_IN_INDEX=TRUE

• From PeopleTools 8.48 the descending keyword came back because this was the first version of PeopleTools that was only certified from Oracle 9i, in which the descending index bug never occurred. (see blog posting 'Descending Indexes are Back!' October 2007).
• In PeopleTools 8.54, there are again no descending indexes because the descending keyword has been omitted from the column list in the CREATE TABLE DDL. You can still specify descending keys in Application Designer because that controls the order in which rows are queried into scrolls in the PIA.  You can also still specify descending order on user indexes, but it has no effect upon either the application or the index DDL.

I haven’t found any documentation that explains why this change has been made.  This time there is no suggestion of a database bug.  However, I think that there are a good database design reasons behind it.

Normally creation of a primary key automatically creates a unique index to police the constraint.  It is possible to create a primary key constraint using a pre-existing index.  The index does not have to be unique, but it may as well.  However, there are some limitations.

• You cannot create primary key on nullable columns - that is a fundamental part of the relational model.  This is rarely a problem in PeopleSoft where only dates that are not marked 'required' in the Application Designer are created nullable in the database.
• You can create a unique index on nullable columns, which is probably why PeopleSoft has always used unique indexes.  
• You cannot use a descending index in a primary key constraint because it is implemented as a function-based index.

CREATE TABLE t (a NUMBER NOT NULL)
/
CREATE UNIQUE INDEX t1 ON t(A DESC)
/
ALTER TABLE t ADD PRIMARY KEY (a) USING INDEX t1
/
ORA-14196: Specified index cannot be used to enforce the constraint.

• We can see that the descending key column is actually a function of a column and not a column, and so cannot be used in a primary key.

SELECT index_name, index_type, uniqueness FROM user_indexes WHERE table_name = 'T'
/

INDEX_NAME INDEX_TYPE                  UNIQUENES
---------- --------------------------- ---------
T1         FUNCTION-BASED NORMAL       UNIQUE 

SELECT index_name, column_name, column_position, descend FROM user_ind_columns WHERE table_name = 'T'
/

INDEX_NAME COLUMN_NAME  COLUMN_POSITION DESC
---------- ------------ --------------- ----
T1         SYS_NC00002$               1 DESC

SELECT * FROM user_ind_expressions WHERE table_name = 'T'
/

INDEX_NAME TABLE_NAME COLUMN_EXPRESSION    COLUMN_POSITION
---------- ---------- -------------------- ---------------
T1         T          "A"                                1

• Non-PeopleSoft digression: you can create a primary key on a virtual column.  An index on the virtual column is not function-based.  So you can achieve the same effect if you move the function from the index into a virtual column, and you can have a primary key on the function.  However, PeopleTools Application Designer doesn't support virtual columns.

I think that descending keys have removed from PeopleTools because:

• It permits the creation of primary key constraints using the unique indexes.
• It does not pose any performance threat.  In Oracle, index leaf blocks are chained in both directions so it is possible to use an ascending index for a descending scan and vice versa. 
• Update 20.4.2016: There is an optimisation in Oracle 11.2.0.4 that improves the performance of the MAX() function in correlated sub-queries on ascending indexes only.  This will benefit all PeopleSoft applications, but especially HCM.

What are the advantages of having a primary key rather than just a unique constraint?

• The optimizer can only consider certain SQL transformations if there is a primary key.  That mainly affects star transformation.
• It allows the optimizer to rewrite a query to use a materialized view. 
• It allows a materialized view refresh to be based on the primary key rather than the rowid (the physical address of the row in a table).  This can save you from performing a full refresh of the materialized view if you rebuild the table (I will come back to materialized views in another posting).
• If you are using logical standby, you need to be able to uniquely identify a row of data otherwise Oracle will perform supplemental logging.  Oracle will additionally log all bounded-size columns (in PeopleSoft, that means everything except LOBs).  Oracle can use non-null unique constraint, but it cannot use a unique function-based index.
• Logical standby can be a useful way to minimise downtime during a migration.  For example, when migrating the database from a proprietary Unix to Linux where there is an Endian change.  Minimising supplemental logging would definitely be of interest in this case.

The descending index change is not something that can be configured by the developer or administrator.  It is just something that is hard coded in Application Design and Data Mover that changes the DDL that they generate.

There are some considerations on migration to PeopleTools 8.54:

• It will be necessary to rebuild all indexes with descending keys to remove the descending keys.  
• There are a lot of descending indexes in a typical PeopleSoft application (I counted the number of function-based indexes in a typical system: HR ~11000 , Financials: ~8500).  If you choose to do this during the migration it may take considerable time.
• In Application Designer, if your build script settings are set to only recreate an index if modified, Application Designer will not detect that the index has a descending key and rebuild it.  So you will have to work out for yourself which indexes have descending key columns and handle the rebuild manually.
• With migration to PeopleTools 8.54 in mind, you might choose to prevent Oracle from building descending indexes by setting _IGNORE_DESC_IN_INDEX=TRUE. Then you can handle the rebuild in stages in advance

Conclusion

I think the removal of descending indexes from PeopleSoft is a sensible change that enables a number of Oracle database features, while doing no harm.