Semi-join Cardinality Underestimated for EXISTS with Multi-column Key
(Doc ID 2752391.1)
Last updated on MARCH 16, 2021
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Information in this document applies to any platform.
Cardinality is underestimated for EXISTS semi-joins on multiple columns with the following issues:
- Differing cardinality estimates based on the order of the join columns in the join clause.
- Cardinality underestimated with the presence of an index or column group on the join columns on one or both sides of the join.
- Setting _optimizer_enable_extended_stats=false leads to underestimated cardinality even when no indexes or column groups are present.
The issue is reproducible even with a simple query with an EXISTS, and can lead to bad plans.
The following cases show the issue:
Upgrade to 12.1 up through 19c (tested).
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