Semi-join Cardinality Underestimated for EXISTS with Multi-column Key (Doc ID 2752391.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 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

Symptoms

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