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.
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. |