Sort/merge vs. Nested Loop

In general, what are the pros/cons of using nested loops versus a sort merge for a join?

I would expect that if one has a huge table (100M rows) and are joining to a small table (1000 rows), that a nested loop using the huge table as the outer loop would be preferable to a sort/merge because the small table would easily fit into memory.

If this is correct, when is the proper time to use a sort/merge join? When the table sizes are not so skewed? When neither table will fit easily into memory? ????

Krister D. Ungerboeck Received on Fri Apr 12 1996 - 00:00:00 CEST