you should open a SR for a traceability on modifying a pega shipped DB table- for future tracking and consultation. I have personally done indexing on the pyuseridentifier on pr_operators; it may affect performance (positively)- if there is an extensive usage/manipulation on the operator ID table or you have reporting capabilities - it definitely will help in getting speed up.
Posted: 7 years ago
Posted: 29 Jul 2016 4:23 EDT
Lochana Durgada Vijayakumar (Lochan_DV)
Senior Manager, Knowledge Management
Any addition indexes that you create in the Pega schema, other than that comes with OOTB scripts, we call them as custom index’s. We (Pega) don’t restrict you from creating new index’s, you can always evaluate the necessary of creating new index’s whenever required to address the performance issue.
Please note that it’s not advisable to create any custom index’s in production environment until and unless your DBA justifies the index requirement. Such changes should b tested in the lower environment before going to the production.
pyUserIdentifier is key property of Data-Admin-Operator-ID class.
When we use Obj-Open by pyuseridentifier SQL is:
select * from DEVGBLD.pr_operators where pzInsKey = ?
pzInsKey is PK on this table and by default there is a index on it. That's why there is no index on pyuseridentifier column. IMHO in same cases we can create index on pyusername. Be cerful of it - if you use autocomplite component with report that "contains" filter criteria on this property SQL is:
select * from DEVGBLD.pr_operators where pzusername like %?%
and index is useless (not used)