Join Hints enforce query optimizer to use a specified join algorithm.
The characteristics of the algorithms are as follows:
| Name | Pros | Cons |
|---|---|---|
| Nested Loops | - Very fast on “Small driven table” - Less consume of memory and disk (suitable for OLTP) - Available for non-equijoins joins |
- Unsuitable for large tables join |
| Hash | - Suitable for large tables join | - Expensive for memory (unsuitable for OLTP) - Unavailable for non-equijoins joins |
| Sort Merge | - Suitable for large tables join - Available for non-equijoins joins |
- Expensive for memory (unsuitable for OLTP) - Unavailable for non-equijoins joins - Inefficient fro unsorted tables. |
Optimum algorithms
Based on table size, the proper algorithms for each join query are as follows:
Small to Small
“Nested Loops”Small to Large
“Nested Loops” with using an index of the large table’s join key.
In case of many rows, exchange the driven table from the small one to the large one. Or “Hash” algorithm might be better solution.Large to Large
“Hash” should be the first try. If the join key has been sorted, then try “Sort Merge”