Nested Loops etc.

Nested loops are
- generally good at getting the first row back
- OK if the tables joined are not so big

If you have an outer join and the optimizer decides to use nested loops, then the driving table must be the table without the (+).

So outer joins limit the options available to the optimizer. This is the reason for the rule "Don't use outer Joins if they are not necessary."

Hash joins are excellent at joining two large sets or a small set with a large set. Depending on how many rows the tables have, a hash join is often 100 or 1000 times faster than a nested loops.

Sort-merge joins, in general, are useful in non-equijoin operations.

These informations are from the book: "Effective Oracle by Design" by Thomas Kyte, McGraw Hill 2003. Still the best book about building performant databases: Very good readable. And since 2003 no other book has been wirtten covering this thema completely.