My Oracle Support Banner

Low Cardinality Estimate Causes NL When Complex Views are Joined to Tables (Doc ID 2744011.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.

Symptoms

The use case for this scenario has multiple complex views where the base tables do not have indexes on all the columns in the predicate; therefore, only FTS access can be done (database is on Exadata, so Exadata may create smart indexes). When the views are joined to other tables, they get an estimated cardinality of 1, which causes nested loops (in actuality more than a hundred thousand rows are returned).

By default we flatten everything out, and after performing the first join, continue joining the results to another table, one at a time.  A bad plan results in the query taking hours or never finishing.

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
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.