Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Oracle optimizer is ignoring some of the indexes

SA-40725

Summary



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.


Error Messages



Not Applicable


Steps to Reproduce



Not Applicable


Root Cause



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.

Resolution



Make the following change to the operating environment: Follow the recommendation in this PDN article.
https://pdn.pega.com/troubleshooting-how-solve-similar-characters-issue-sql-query-oracle-database

Suggest Edit

Published August 26, 2017 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us