Slow Performance Due To Hash Group By Placement With Subquery Factoring Involving Large Partitioned Tables
(Doc ID 2565285.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 19.1.0.0.0 [Release 11.2 to 18] Information in this document applies to any platform.
Symptoms
Subquery factoring (WITH clause queries) involving large partitioned tables and group by run slow when materialized (using hint /*+ materialize */ or _with_subquery=materialize).
HASH GROUP BY is placed above the PARTITION ITERATOR ALL resulting in huge TEMP space and slow SQL performance.
When the subquery is executed inline (/*+ INLINE */ or _with_subquery=inline), the HASH GROUP BY happens below the partition iterator results in faster SQL performance.
Changes
Upgrade to 11.2.0.4 or 12.2 till 19.1.
Cause
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
