We have identified some Pega product tables with huge amount of data and a poor performing SQL that runs several times a day. Our performance DBA's have recommended an index to improve the performance of a frequently used SQL, would like to get some inputs on the index recommended and if its ok to create a index on the product database. Keep in mind we are on Pega Robot Manager 4 on 7.3.1 platform.

Problem SQL: SQLTEXT: SELECT "PC0"."PXTIMECREATED" AS "pxTimeCreated" , SUBSTR("PC0"."PXHISTORYFORREFERENCE", CAST(:1 AS INT)) AS "pyTextValue(1)" , "PC0"."PYMEMO" AS "pyMemo" , "PC0"."PXOBJCLASS" AS "pxObjClass" , "PC0"."PYLABEL" AS "pyLabel" , "PC0"."PZINSKEY" AS "pzInsKey" FROM PEGA_DATA_O.pr_history_data "PC0" WHERE ( UPPER("PC0"."PXHISTORYFORREFERENCE") = :2 AND ( "PC0"."PYLABEL" IS NOT NULL OR "PC0"."PYMEMO" LIKE :3 ) ) AND "PC0"."PXOBJCLASS" = :4 ORDER BY 1 DESC Index Recomended Recommendation (estimated benefit: 81.74%) ------------------------------------------ - Consider running the Access Advisor to improve the physical schema design or creating the recommended index. create index PEGA_DATA_O.IDX$$_03350001 on PEGA_DATA_O.PR_HISTORY_DATA(UPPER("PXHISTORYFORREFERENCE")); Tables with Large amount of Data TABLE_NAME NUM_ROWS PR_LOG_SECURITYAUDIT_ARCH 186,826,228 PR_HISTORY_DATA_ARCH 72,285,115 PR_LOG_SECURITYAUDIT 25,362,721 PR_SYS_QUEUE_FTSINDEXER 23,826,515 PR_HISTORY_DATA 21,811,029 PR_PERF_STATS 17,500,294 PR_HISTORY 12,444,754 PR_DATA 754,165 PR_INDEX_EMAIL 753,011 PR_DATA_SVCINVHIST 122,368