When looking at an explain plan to tune a long running SQL which has lots of joins we see three types in the explain plan.
(1.) Nested loops
(2.) Hash Joins
(3.) Sort-Merge joins
When, under which circumstances is it good to use these joins?
For example, I have heard that if you join 2 large tables, each with millions of rows, then the best method is nested loops.
If one table has million rows and the other has only few hundred, then a hash join is the best.
If both tables have small number of rows, say few thousand, then sort-merge is the best.
Is this true, and are their any guidelines to this procedure?
Also, how can we change the optimizer to use a particular join method in the explain plan?
For example, if 2 large tables with millions of rows each is using a sort-merge join and if
this is the cause of the performance issue, how is it possible to change it to use a nested loop join?
What changes should we do to the SQL?