Hitachi

Hitachi Advanced Database Application Development Guide


5.6.4 Characteristics of the methods for processing subqueries that contain an external reference column

The following table describes the characteristics of each method for processing subqueries that contain an external reference column.

Table 5‒7: Methods for processing subqueries that contain an external reference column

No.

Processing method

Benefits

Disadvantages

1

Nested loops work table execution

B-tree indexes or text indexes can be used for subquery search conditions that contain an external reference column. This allows data to be retrieved at a higher speed when B-tree indexes or text indexes are used to narrow the search range.

Processing performance decreases when the hit count of the queries outside the subquery is high.

2

Nested loops row value execution

3

Hash execution

Data can be retrieved at a higher speed when all data required for the processing can be stored in the hash table.

If a large amount of data must be stored in the hash table, the size of the hash table area becomes large. Processing performance decreases if a shortage occurs in the hash table area, because all data is first saved to a work table.