My Oracle Support Banner

Low Cardinality Estimate Causes NL When Complex Views are Joined to Tables (Doc ID 2744011.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


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.


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.