My Oracle Support Banner

SQL With Left Outer Join is Slower and Showing Different Behavior In 12c (Doc ID 2284617.1)

Last updated on MARCH 28, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 12.1 to 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
This is not a Wrong Results problem. The results in both 11g and 12c are correct. Applying a Profile or Outline does not necessarily change the behavior. Setting optimizer_features_enabled to a lower value such as makes no difference to this changed behavior.


The following example SQL with a LEFT OUTER JOIN show the same plans in and but timings and behavior is different.

11g- It results in 5 seconds [Sleep once for all records]
12c - It results in 20 seconds [Sleep once for each record 4x5 = 20 secs]

The significant difference in this plan is that the number of Starts for line 6 is 4 (once for each of "1", "2", "3" and "4"). The use of the Lateral View can be eliminated but makes no difference. Since, as in this case, the FUNC_WAIT function waits 5 seconds and there are 4 starts of line 6 the SQL will wait for a total of 20 seconds (5 seconds x 4 Starts).


 There were no code changes from 11g to 12c. ( and 12.2.01).


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

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