5.6.2 Characteristics of the methods for processing subqueries that do not contain an external reference column

1

Work table execution

This method can be applied to the conditions of all subqueries that require work tables.

Processing performance decreases when there are many queries outside the subquery.

2

Row value execution

B-tree indexes or text indexes can be used for queries outside the subquery. When there are many such queries, 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 there are many queries outside the subquery and the predicates containing the subquery cannot be narrowed down by using B-tree indexes or text indexes.

3

Work table row value execution

B-tree indexes or text indexes can be used for queries outside the subquery. When there are many such queries and the subquery hit count is low, 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 subquery hit count is high, because B-tree indexes or text indexes are used for as many queries outside the subquery as there are rows resulting from the subquery.

4

Hash execution

Data can be retrieved at a higher speed if 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.