Slow Performance Due To Hash Group By Placement With Subquery Factoring Involving Large Partitioned Tables
(Doc ID 2565285.1)
Last updated on MARCH 18, 2022
Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168.0 [Release 11.2 to 18]
Information in this document applies to any platform.
- 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.
Upgrade to 22.214.171.124 or 12.2 till 19.1.
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.