Monday, February 16, 2015

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.

7 comments :

Eric said...

A member of my DBA team has proposed an alternative to the Oracle method for dealing with descending indexes:

Oracle method:
Set parameter.
Drop DESC indexes.
Recreate DESC indexes (DESC keyword still present in DDL).

His method:
Set parameter
Create a copy of the DESC index as an invisible index (no DESC keyword).
Drop DESC index.
Rename the invisible index.
Make invisible index visible.

Will this new method accomplish the same results as the Oracle method?

David Kurtz said...

Yes, and it has the additional benefit that you will never not have a unique index, though you will have to maintain both for a while during DML.

Eric said...

Thank you, David. We have found in the past that while delivered scripts will do the job, they are often not the most efficient.

Anonymous said...

I have recently come accross this approach from Oracle
https://docs.oracle.com/cd/E58500_01/pt854pbh1/eng/pt/tadm/task_ConvertingDescendingIndexes.html#topofpage

As of PeopleTools release 8.54, to optimize performance, descending indexes are no longer supported. If you are upgrading from an earlier version of PeopleTools, you must convert any descending indexes to ascending indexes by dropping descending indexes and recreating them as ascending indexes.

The following scripts are provided in \SCRIPTS\ORA\ to accomplish this task:
postupgcreatedescindexes.sql
postupgdropdescindexes.sql

To convert descending indexes to ascending indexes, complete these steps:

Connect as AccessId, run postupgdropdescindexes.sql

Connect as AccessId, run postupgcreatedescindexes.sql

Connect as AccessId, run psdropdescindexes.sql

Connect as SYSDBA and run alter system set "_ignore_desc_in_index"=true;

Connect as AccessId, run pscreatedescindexes.sql

Connect as SYSDBA and run alter system set "_ignore_desc_in_index"=false;


the script pscreatedescindexes.sql creates the indexes with the same desc in the index.
If we are recreating the indexes the same as before they were dropped what is the change here.

what exactly is the parameter change helping with?

thank you.

David Kurtz said...

The only purpose of the parameter is to render the DESCENDING keyword meaningless in the CREATE INDEX statement.

Interestingly, Oracle Support Document 1909646.1: Removing Descending Indices for PeopleSoft Databases suggests setting this parameter in PT8.48 to 8.53 as it is beneficial for performance. However, it is not a mandatory parameter.

Despite a reported problem in Oracle 11.2.0.3 (bug 20212407), I think I would leave it the setting in place to prevent anyone from creating descending key indexes manually.

wydot_dlross said...

Hi David,

We are upgrading from PeopleTools 8.54 to 8.57 and have discovered that we have over 10K Descending Indexes still defined in our database instances (HCM and FSCM). We have removed those. Now we find that there are a few remaining function-based indexes (~20). I haven't been able to get a clear answer from Oracle as to if these are useful or not.

The only relevant information I've found on your blog, etc. is that they are useful for when the UPPER() function is used on search pages.

I believe the these indexes were added by our hosting provider DBA's after analyzing certain SQL statements. I am going to ask for the definitions of those.

Thank you for your valuable insight.

David Kurtz said...

You could look through the execution plans captured by AWR (DBA_HIST_SQL_PLAN) to find any execution plans and hence SQL statements that use these indexes. However, if you don’t find any plans, it does not mean that the indexes are not used, just that the plans were not captured by AWR.
It may be that some of the remaining function-based indexes are useful and you decide to retain them. However, there is a cost of ownership. You won’t be able to bring them into Application Designer, they will always have to be managed manually. They will always appear in DDDAUDIT. You will also have to be careful that the indexes are not dropped by Application Designer. This is something that might happen when altering the tables involved because they are not defined in the PeopleTools meta-data. The only way I can think of protecting them is with a DDL trigger – see DDL Triggers to prevent loss of database objects not managed by PeopleTools