How To Improve Performance of SQL Statements with many Correlated Subqueries on Oracle Using the Unnest Hint (Doc ID 2009146.1)

Last updated on MARCH 16, 2023

Applies to:

PeopleSoft Enterprise HCM Global Payroll Core - Version 9.2 and later
Information in this document applies to any platform.

Goal

When a SQL statement is constructed with a driving query and many correlated sub-queries, it can perform quite badly when Oracle often does not estimate how effective the correlated sub-queries will be in filtering out data returned by the driving query. This document explains a technique using the /*+ UNNEST */ hint that can make these types of statements to perform much better.

The specific example used below comes from the PeopleSoft Global Payroll Calculate Absence and Payroll process, typical of COBOL Stored Statement named GPPSERVC_I_HISRWRK and GPPSERVC_I_HISTWRK. When this technique is completed for a statement, it needs to replace the delivered statement, the specifics of that technique are beyond scope of this document.

This document offers a technique to identify the most selective correlated sub-query block and then use a /*+ UNNEST */ hint that will allow that sub-query block to be processed more efficiently.

Note: This technique can help in most cases, but after it has been explored in a specific environment and found to be insufficient, it should not be used.