Oracle optimizer is ignoring some of the indexes
Some of the Primary key/indexed columns in Pega table are 255 characters in length. Oracle optimizer is ignoring some of the indexes because the index values are more than 32 bytes and the first 32 bytes are the same for most rows. Oracle optimizer relies on histograms and histograms only store first 32 bytes
Based on the data collected column PZINSKEY and PXLINKEDREFFROM are used often in the queries and both are varchar2 255 length.
This causes latency due to table scan.
Steps to Reproduce
A third-party product issue. Oracle uses histograms (statistics about distinct values in columns) to determine how unique, and therefore how "selective", the data in a given column is. The histograms are calculated on the first 32 characters of a column. If a column has all its values identical in the first 32 characters, the histogram distribution for this column is set to 1, which means "non-selective".
When determining the query plan for a SQL statement that uses this column in a WHERE clause, the database optimizer will choose not to select a perfectly valid index due to the histogram reporting it as non-selective.
Make the following change to the operating environment: Follow the recommendation in this PDN article.