This article describes a script on my website (psredundantfix.sql) that uses a similar query to that described in the previous article to identify redundant indexes from the metadata in the PeopleTools tables. It uses an anonymous block of PL/SQL so nothing is installed in the database. For each redundant index identified it:
- Adds the record and redundant index into an Application Designer project called REDUNDANT INDEXES. The record must also be added, otherwise Application will not generate a DDL script to remove the index.
- Unsets the platform flag on the redundant index if the superset index is active on the same platform. Thus Application Designer will generate a script to drop redundant indexes when the project is built. The redundant index definition remains in the record definition in case there is a need to revert the change.
- If the superset index is active on Oracle
- Creates and collects extended optimizer statistics on the combination of columns in the redundant index. If error ORA-00054 occurs, the exception will be ignored and the script will continue.
- Makes the index invisible. Note that indexes are not dropped. That can be done later by building an alter script for the project. It would be normal to test the consequences of making the indexes invisible for a while, and drop them later.
Project REDUNDANT INDEXES already exists … Record PSPROJECTITEM, Redundant Index C already in project Creating Extended Statistics on PSPROJECTITEM for OBJECTVALUE4,OBJECTTYPE Making index PSCPSPROJECTITEM invisible ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired … Record PSTREENODE, Redundant Index C already in project … Record TL_RPTD_TIME, Redundant Index C added to project Making index PSCTL_RPTD_TIME invisible Record TL_TA_BATCH, Redundant Index _ added to project Making index PS_TL_TA_BATCH invisible Making index PS_TL_TA_BATCH1 invisible … Making index PS_TL_TA_BATCH48 invisible … Record WRK_XREF_CALC, Redundant Index A already in project Creating Extended Statistics on PS_WRK_XREF_CALC for PROCESS_INSTANCE,SEQ_NBR Creating Extended Statistics on PS_WRK_XREF_CALC1 for PROCESS_INSTANCE,SEQ_NBR … Creating Extended Statistics on PS_WRK_XREF_CALC6 for PROCESS_INSTANCE,SEQ_NBR Making index PSAWRK_XREF_CALC invisible Making index PSAWRK_XREF_CALC1 invisible … Making index PSAWRK_XREF_CALC6 invisible
- The script commits any changes it makes. There is also a rollback command at the top to prevent it accidentally committing something else.
- Having run it once, should you immediately run the script again, nothing will be found because any redundant indexes have been marked as disabled on Oracle
- Should you want to revert changes, mark all indexes in the REDUNDANT INDEXES project as active on Oracle.
We can see in Application Designer that index PSAWRK_XREF_CALC is a subset of PS_WRK_XREF_CALC because both start with PROCESS_INSTANCE and SEQ_NBR.
UPDATE psindexdefn SET platform_ora = 1 , activeflag = 1 WHERE (recname, indexid) IN ( SELECT objectvalue1, objectvalue2 FROM psprojectitem WHERE objecttype = 1 AND projectname = 'REDUNDANT INDEXES') AND platform_ora = 0; COMMIT;
Index A was marked inactive on all platforms because the superset index _ is active on all platforms.
PeopleSoft delivers some indexes for some platforms only. For example, PSETREENODE is only active on Informix. Therefore the script only deactivates the Informix platform flag on PSCTREENODE, and the platform flag changes to 'Some'. The index is still added to the project, but no attempt is made to create extended statistics or to make it invisible, and the index continues to be built on Oracle.
The comment applied to index C indicates that only the Informix flag was changed.
The final step, when you are satisfied that making the indexes invisible has no unacceptable consequences, is to drop the redundant indexes using the script generated by Application Designer
… DROP INDEX PSCPSPROJECTITEM / DROP INDEX PSEPSPROJECTITEM / … DROP INDEX PSAWRK_XREF_CALC / DROP INDEX PSAWRK_XREF_CALC1 / … DROP INDEX PSAWRK_XREF_CALC6 /