Nonstop Database, HiRDB Version 9 UAP Development Guide
The following table shows the execution methods and features of subqueries that have external references.
Table 4-15 Execution methods and features of subqueries with external references
Execution method | Processing method | Advantages | Disadvantages |
---|---|---|---|
Nested loops work table execution | Each time a row of the external query is searched, this method executes the subquery, creates a work table, and evaluates the condition that includes the subquery. | An index can be used for the subquery search conditions that include a reference column to the outside. Therefore, data can be searched rapidly when a subquery search condition can narrow the search range by using an index. In external query searches, the subquery search can be omitted when the external reference column repeatedly searches a row of the same value. |
The performance drops when the number of external query hits is high. |
Nested loops row value execution | Each time a row of the external query is searched, this method executes the subquery (a work table is not created) and evaluates the condition that includes the subquery. | An index can be used for the subquery search conditions that include a reference column to the outside. Therefore, data can be searched rapidly when a subquery search condition can narrow the search range by using an index. In external query searches, the subquery search can be omitted when the external reference column repeatedly searches a row of the same value. |
The performance drops when the number of external query hits is high. |
Hash execution | This method creates a hash table from the subquery results beforehand. Then each time a row is fetched from the external query, this method hashes the values of the external query and matches them with the hash table. | Data can be searched rapidly when the number of subquery hits, excluding conditions that include external reference columns, is low and the number of external queries is high. | An index cannot be used for conditions that include an external reference column. If the hit count for subqueries that exclude conditions that include an external reference column is high, the size of the work table buffer used becomes large. Although the work table buffer size to be used can be specified, the buffer data must be saved to a work table file when the work table buffer becomes full, and consequently the performance drops. If subqueries are to be joined, conditions that include external reference columns are evaluated after the subqueries are joined. |
Figure 4-51 NESTED LOOPS WORK TABLE SUBQ processing method
SELECT C1 FROM T1 WHERE C1=ANY(SELECT C1 FROM T2 WHERE C2=T1.C2)
Figure 4-52 NESTED LOOPS ROW VALUE SUBQ processing method
SELECT C1 FROM T1 WHERE C1=(SELECT MAX(C1) FROM T2 WHERE C2=T1.C2)
Figure 4-53 HASH SUBQ processing method
SELECT T1.C1 FROM T1 WHERE EXISTS(SELECT * FROM T2 WHERE C1='a' AND C2=T1.C2)
SELECT T1.C1 FROM T1 WHERE T1.C3<(SELECT T2.C3 FROM T2 WHERE C1='a' AND C2=T1.C2)
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.