5.5.3 Characteristics of the joining methods
The following table describes the characteristics of the two joining methods.
|
Joining method |
Speed of the initial data retrieval operation |
Benefits |
Disadvantages |
|---|---|---|---|
|
Nested loop join |
Fast |
Retrieval is faster when the joined column in the inner table can be narrowed down using a B-tree index or text index. |
If there are many hit rows in the outer table, processing performance decreases. |
|
Hash join |
Slow |
If there are few hit rows in the outer table and many hit rows in the inner table, high-speed retrieval can be achieved. |
If there are many hit rows in the outer table, the hash table area might become large. If a shortage occurs in the hash table area, processing performance decreases because the data is first saved to a work table. However, if the outer table is a multi-chunk table and HADB determines that it can apply hash join division execution on a per-chunk basis, the likelihood of needing to offload to a work table decreases (reducing the possibility of performance degradation). |