Session-level optimizer parameter to disable Nested Loops joins

Danilo Piazzalunga

On a large data warehouse and when running non-interactive, batch jobs, nested loops joins are almost always a bad idea.

Most often, they result from an incorrect execution plan based on incorrect cardinality estimates, caused by incorrect and/or stale statistics.

There should be either a session-level or a system-level parameter (or both) to instruct the optimizer never to use a nested loops join, as if the NO_USE_NL hint was being used on every table in the SQL query.

William Robertson

Agree in principle, however you'd also want to disable Merge Join Cartesian and probably some other things I haven't thought of.