Common
Cases to Consider
•Nested loops reach a
medium-sized fraction of a large joined-to table: Optimizer will
mildly 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.
–In this common case,
nested-loops are frequently preferable to the hash-join choice of
the optimizer, largely owing to self-caching in queries of naturally
clustered tables.