My Oracle Support Banner

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 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Cardinality is underestimated for EXISTS semi-joins on multiple columns with the following issues:

  1. Differing cardinality estimates based on the order of the join columns in the join clause.
  2. Cardinality underestimated with the presence of an index or column group on the join columns on one or both sides of the join.
  3. 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:

 

Changes

 Upgrade to 12.1 up through 19c (tested).

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!


In this Document
Symptoms
Changes
Cause
Solution
References


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