How To Improve Performance of SQL Statements with many Correlated Subqueries on Oracle Using the Unnest Hint
Last updated on JANUARY 10, 2017
Applies to:PeopleSoft Enterprise HCM Global Payroll Core - Version 9.2 and later
Information in this document applies to any platform.
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms