Common
Cases to Consider
•Nested
loops reach a tiny fraction of a large joined-to table, but the
optimizer greatly over-estimates that
fraction: Optimizer would prefer a nested-loops join, if it
knew more, and this is much faster than the hash-join
alternative.
–This
tends to happen for “exceptions” reports, where two conditions are
almost mutually exclusive, but the optimizer’s assumption of
statistically independent conditions yields the over-estimate.
–As
humans, we can and should expect reasonable rowcounts in reports, so we
should often expect this case.