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

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.