The 12.2 optimizer does not consider concatenation with window function as it does in earlier version (Doc ID 2433746.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

A SQL is much slower after upgrading from 12.1.0.1 to 12.2.0.1, consuming more CPU
Following is the plan difference:
12.1.0.1 Plan:

5 64 .... WINDOW SORT PUSHED RANK [+] 76979 69221 1 0 [+]
6 63 ....+ CONCATENATION [+] 74990 1 72306086
7 31 ....+. HASH JOIN [+] 37495 38725 1 72306086 *** 1867x under [+]

12.2.0.1 explain plan:

5 32 .... WINDOW SORT PUSHED RANK [+] 126478655 1344025
6 31 ....+ NESTED LOOPS 126331850 1344025

	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

Symptoms

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