Comparing Join Algorithms
There is no one best algorithm.
The choice depends on input sizes, available indexes, and even data
distributions.
Consider three scenarios:
| Scenario |
Description |
NR |
pR |
NS |
pS |
B |
| Analytic, Reporting |
Join large tables for later aggregation. |
1000 |
20 |
1000 |
20 |
100 |
| Interactive |
Join related tables for a single entity (customer, product, etc.) |
1 |
1 |
1000 |
20 |
100 |
| Include small reference table |
E.g. Include a table of US States or Job Categories. |
1000 |
20 |
1 |
50 |
100 |
|