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