My Oracle Support Banner

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

Last updated on OCTOBER 22, 2018

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.

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
 Original Query
 Main Query - Sanitized And Formatted
 Distilling The Driving Query
 Enumerating The Sub-Queries
 Identifying The Most Selective Sub-Query
 Un-Nesting The Sub-Query
 Re-assembling the Final Statement
 Replacing Original Statement


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.