Hitachi

Hitachi Advanced Database Application Development Guide


5.5.3 Characteristics of the joining methods

The following table describes the characteristics of the two joining methods.

Table 5‒5: Characteristics of the table joining methods

Joining method

Speed of the initial data retrieval operation

Advantage

Disadvantage

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.