Low Cardinality Estimate Causes NL When Complex Views are Joined to Tables
(Doc ID 2744011.1)
Last updated on APRIL 09, 2021
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 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