Removing Redundant Indexes in PeopleSoft

This is the second of a two-part article that discusses how to identify and remove redundant indexes from a PeopleSoft system. 
This article describes a script on my website (psredundantant.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. 

Sample Output

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

Note:

• 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.

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;

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.


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
/

Identifying Redundant Indexes in PeopleSoft

This is the first of a two-part series that discusses how to identify and remove redundant indexes from a PeopleSoft system.
I recently wrote a series of articles on my Oracle blog about identifying redundant indexes on an Oracle database. The same approach can be taken with PeopleSoft, but using the metadata in the PeopleTools tables rather than the database catalogue.
(This means you could take a similar approach for PeopleSoft on databases other than Oracle, but I can't say anything about the behaviour of other optimizers) 
The following query returns a report of superset and redundant subset indexes in a PeopleSoft system. It can be downloaded as a script from my website (psredundant.sql).
The generic Oracle version of this script will identify subset indexes that are unique as redundant because the primary key and unique constraints can be altered to use the superset index.  However, this query does not make subset unique indexes as redundant because PeopleSoft does not explicitly create unique constraints, only unique indexes.  If I disabled a unique index, I would not be able to maintain the unique constraint via Application Designer.
Subquery IC returns a list of columns for each index. Subqueries UNI, AS2 and USI retrieve columns for unique/duplicate key, alternate search and user defined indexes respectively. The absolute value of key position is not important for this exercise, it is merely used to sort the column list in the listagg() function in subquery I.

REM psredundant.sql
set lines 160 pages 50 trimspool on
column recname format a15
column superset_index format a60
column redundant_index format a60
break on recname skip 1 on indexid skip 1
spool psredundant

WITH uni AS (/*unique indexes*/
SELECT /*+MATERIALIZE*/ f.recname, i.indexid
,      MIN(i.uniqueflag) OVER (PARTITION BY f.recname) uniqueflag
,      CASE WHEN MAX(CASE WHEN f.recname != f.recname_parent THEN 1 ELSE 0 END) OVER (PARTITION BY f.recname)=1 
        THEN f.fieldnum ELSE k.keyposn END keyposn
,      k.fieldname
FROM   psrecfielddb f
,      psindexdefn i
,      pskeydefn k
WHERE  i.recname IN(f.recname,f.recname_parent)
AND    i.recname = k.recname
AND    k.fieldname = f.fieldname
AND    i.indexid = '_' /*unique index*/
AND    k.indexid = i.indexid
AND    bitand(f.useedit,3) > 0 /*unique or dup key*/
), as0 AS (/*leading column on alternate search indexes*/
SELECT f0.recname, k0.indexid, i0.uniqueflag, 0 keyposn, f0.fieldname
FROM   psrecfielddb f0
,      psindexdefn i0
,      pskeydefn k0
WHERE  bitand(f0.useedit,16) = 16 /*alternate search key*/
AND    k0.recname = f0.recname_parent
AND    k0.fieldname = f0.fieldname
AND    i0.recname = k0.recname
AND    i0.indexid = k0.indexid
AND    i0.indexid BETWEEN '0' AND '9' /*alternate search index*/
), as1 AS ( /*now add unique columns*/
SELECT as0.recname, as0.indexid, as0.uniqueflag, as0.keyposn, as0.fieldname
FROM   as0
UNION ALL /*append unique key index columns*/
SELECT as0.recname, as0.indexid, as0.uniqueflag, uni.keyposn, uni.fieldname
FROM   as0, uni
WHERE  as0.recname = uni.recname
), as2 AS (
SELECT as1.recname, as1.indexid, as1.uniqueflag, NVL(k.keyposn,as1.keyposn), as1.fieldname
FROM   as1
       LEFT OUTER JOIN pskeydefn k /*to handle custom key order*/
       ON  k.recname = as1.recname
       AND k.indexid = as1.indexid
       AND k.fieldname = as1.fieldname
), usi AS (/*user indexes*/
SELECT i.recname, i.indexid, i.uniqueflag, k.keyposn, k.fieldname
FROM   psindexdefn i
,      pskeydefn k
WHERE  k.recname = i.recname
AND    k.indexid = i.indexid
AND    k.indexid BETWEEN 'A' AND 'Z'
AND    i.platform_ora = 1
), m AS (/*merge three kinds of index here*/
SELECT uni.recname, uni.indexid, uni.uniqueflag, uni.keyposn, uni.fieldname
FROM   uni
UNION ALL
SELECT as1.recname, as1.indexid, as1.uniqueflag, as1.keyposn, as1.fieldname
FROM   as1
UNION ALL
SELECT usi.recname, usi.indexid, usi.uniqueflag, usi.keyposn, usi.fieldname
FROM   usi
), ic AS ( /*list of columns, restrict to tables*/
SELECT r.recname, m.indexid, m.uniqueflag, m.keyposn, m.fieldname
FROM   m
,      psrecdefn r
WHERE  r.rectype IN(0,7)
And    r.recname = m.recname
), i AS ( --construct column list
SELECT /*+ MATERIALIZE*/
       ic.recname, ic.indexid, ic.uniqueflag
,      count(*) num_columns
,      listagg(ic.fieldname,',') within group (order by ic.keyposn) AS fieldlist
FROM   ic
GROUP BY ic.recname, ic.indexid, ic.uniqueflag
)
SELECT r.recname
,      i.indexid||' ('||i.fieldlist||')' superset_index
,      r.indexid||' ('||r.fieldlist||')' redundant_index
FROM   i
,      i r
WHERE  i.recname = r.recname
AND    i.indexid != r.indexid
AND    r.uniqueflag = 0 /*non-unique redundant*/
AND    i.fieldlist LIKE r.fieldlist||',%'
AND    i.num_columns > r.num_columns
order by r.recname, r.indexid
/

spool off

This is part of the output for a HR 9.2 demo database. In all it identified 58 indexes on 41 records. However, some of those records are temporary records with several physical instances, so the number of database objects that will be removed is higher.

RECNAME         SUPERSET_INDEX                                               REDUNDANT_INDEX
--------------- ------------------------------------------------------------ -----------------------------------------------------------
…
HRS_APP_PROFILE C (HRS_AL_LOG_ID,HRS_PERSON_ID)                              B (HRS_AL_LOG_ID)

HRS_SJT_JO      B (HRS_JOB_OPENING_ID,SCRTY_TYPE_CD)                         A (HRS_JOB_OPENING_ID)

HR_PROMOTE_XREF C (EMPLID,EMPL_RCD)                                          A (EMPLID)
                B (EMPLID,EMPL_RCD,ACTION_DT_SS,EFFSEQ)                      A (EMPLID)
                B (EMPLID,EMPL_RCD,ACTION_DT_SS,EFFSEQ)                      C (EMPLID,EMPL_RCD)

HR_SSTEXT_EFFDT _ (OBJECTOWNERID,HR_SSTEXT_SUB_ID,TEXT_ID,EFFDT)             A (OBJECTOWNERID,HR_SSTEXT_SUB_ID,TEXT_ID)

HR_TRANSFR_XREF C (EMPLID,EMPL_RCD)                                          A (EMPLID)
                B (EMPLID,EMPL_RCD,ACTION_DT_SS,EFFSEQ)                      A (EMPLID)
                B (EMPLID,EMPL_RCD,ACTION_DT_SS,EFFSEQ)                      C (EMPLID,EMPL_RCD)

PA_ALIAS_USE    0 (ALIAS_TYPE,ALIAS_NAME)                                    A (ALIAS_TYPE)

…
WRK_XREF_CALC   _ (PROCESS_INSTANCE,SEQ_NBR,XREF_NUM)                        A (PROCESS_INSTANCE,SEQ_NBR)

The articles on my Oracle blog discuss how to identify redundant indexes on an Oracle database, and then how to go about removing them.  Of course, in PeopleSoft, you should only remove indexes by making the change within Application Designer, and that is what the next posting discusses.