A Rare
Case to Consider
•Nested
loops reach a large fraction of a large joined-to table: Optimizer
will strongly (and usually correctly) prefer a hash join to a full
table scan, taking into account the benefit of multi-block
reads, and avoiding multiple logical I/Os to the same blocks.
–When
the join order is correct, this almost always implies a huge set of
rows being returned by the query.
–Such
large return rowsets are very rarely useful for human consumption,
but can reasonably occur in middleware or conversion processes.