Issue with PXCREATEDATETIME mapping to DATE data type
The Reporting performance degrades when using the PXCREATEDATETIME in the Where clause.
Pega application is a multi-tenant and work tables are partitioned based on the tenant and sub-partitioned using the PXCREATEDATETIME. Most reports (real-time and offline) use PXCREATEDATETIME.
The sub-partition is ignored when the Pega application sends milliseconds part of PXCREATEDATETIME (which is mapped to the Date datatype in Oracle).
Database used: Oracle 12G
Steps to Reproduce
- Create a subpartition on the PXCREATEDATETIME in the work table.
- Run the report with PXCREATEDATETIME in the Where clause.
The Pega application sent milliseconds to the JDBC Driver API irrespective of the DATE or the TIMESTAMP column type because it used the same function call for both. The Driver API formatted the data accordingly as per the column type in the database.
Perform the following local-change:
Change the column type to TIMESTAMP. It is recommended to revise the partitioning approach.
0% found this useful