Converting Descending Indexes in PeopleTools 8.54

From PeopleTools 8.54 release, Oracle descending indexes are no longer supported. When upgrading from a previous PeopleTools release, these descending indexes need to be recreated as ascending indexes by first identifying them, dropping them and then creating them again.

You might also want to make sure your upgrade plan accommodates considerable time towards this effort because the number of such indexes in applications like HCM and FSCM is also no less. In HCM 9.2, the number of such indexes are close to 10,000; in FMS it is close to 13,000 . Other applications like Portal, ELM have such indexes to be lesser than 500.

Oracle PeopleSoft has provided two scripts (both are located in the scripts folder in PS_HOME) to accomplish the task:

• postupgcreatedescindexes.sql
• postupgdropdescindexes.sql

postupgcreatedescindexes.sql has the following SQL statements to identify the descending indexes in the particular database. When you’ll run this SQL, you’ll be prompted to provide the schema name, which will replace &SCHEMA_NAME mentioned in the script.

WITH distinct_index AS (
SELECT DISTINCT index_name ind_name,table_name tab_name
FROM USER_IND_COLUMNS
WHERE DESCEND='DESC')
SELECT DBMS_METADATA.GET_DDL('INDEX',index_name) txt
FROM user_indexes UI,distinct_index DI
WHERE UI.table_name=DI.tab_name
AND   UI.index_name=DI.ind_name
AND   UI.table_owner='&SCHEMA_NAME'

postupgdropdescindexes.sql has the following SQL commands to drop those indexes:

WITH distinct_index AS (
SELECT DISTINCT index_name ind_name,table_name tab_name
FROM USER_IND_COLUMNS
WHERE DESCEND='DESC')
SELECT 'Drop index '||UI.index_name||';' txt
FROM user_indexes UI,distinct_index DI
WHERE UI.table_name=DI.tab_name
AND   UI.index_name=DI.ind_name
AND   UI.table_owner='&SCHEMA_NAME';

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

1. Connect as AccessId (like SYSADM), run postupgdropdescindexes.sql – it will create an output SQL file, pscreatedescindexes.sql containing DDL statements for dropping all the descending indexes.
2. Connect as AccessId, run postupgcreatedescindexes.sql – it will append the create index DDL statements in same SQL file, pscreatedescindexes.sql,
3. Connect as AccessId and run psdropdescindexes.sql
4. Connect as SYSDBA and run alter system set “_ignore_desc_in_index”=true; — it will prevent Oracle from building descending indexes even if DESC keyword is specified.
5. Connect as AccessId, run pscreatedescindexes.sql
6. Connect as SYSDBA and run alter system set “_ignore_desc_in_index”=false;