Table 4-13 shows the execution methods and features of subqueries that have external references.

Table 4-13 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.

(a) Nested loops work table execution

NESTED LOOPS WORK TABLE SUBQ

This processing method is applied to table subqueries specified on the right side of quantified predicates and IN predicates.

First, the external query is executed. During the execution, each time a row of the external query is fetched, the values in the external reference column are used to execute the subquery, the values of the subquery selection expression are calculated, and a work table is created. Next, the work table that was created from the subquery is used to evaluate the condition that includes the external subquery.

Because the external query is processed one row at a time, multiple work table areas are never created at the same time. Also, because the subquery is executed for each row in the external query, the performance drops when the external query has many rows.

Figure 4-46 shows the NESTED LOOPS WORK TABLE SUBQ processing method.

Figure 4-46 NESTED LOOPS WORK TABLE SUBQ processing method

Example

SELECT C1 FROM T1
  WHERE C1=ANY(SELECT C1 FROM T2 WHERE C2=T1.C2)

Note

The underlined section is the external reference column.

The external query is executed. The values of the outer reference column (T1.C2) are used to execute the subquery for all rows of the external query, and a work table is created from the T2.C1 values. Next, T1.C1 is matched with the T2.C1 work table, and the condition that includes the subquery is evaluated.

(b) Nested loops row value execution

NESTED LOOPS ROW VALUE SUBQ

This processing method is applied to row subqueries, scalar subqueries, and EXISTS predicates.

First, the external query is executed. During the execution, each time a row of the external query is fetched, the values in the external reference column are used to execute the subquery, and the values of the subquery selection expression are calculated. (A work table is not created.) Next, the values of the subquery results are used to evaluate the condition that includes the subquery of the external query.

Because the subquery is executed for each row in the external query, the performance drops when the external query has many rows.

Figure 4-47 shows the NESTED LOOPS ROW VALUE SUBQ processing method.

Figure 4-47 NESTED LOOPS ROW VALUE SUBQ processing method

Example

SELECT C1 FROM T1
  WHERE C1=(SELECT MAX(C1) FROM T2 WHERE C2=T1.C2)

Note

The underlined section is the external reference column.

The external query is executed. The values of the outer reference column (T1.C2) are used to search the subquery for all rows of the external query, and the MAX(T2.C1) value is fetched. (A work table is not created.) Next, the condition that includes the subquery found in the external query is evaluated.

(c) Hash execution

HASH SUBQ

This processing method applies to table subqueries specified in EXISTS predicates and on the right side of comparison predicates, quantified predicates, and IN predicates.

First, the subquery is executed without the condition that includes the external reference column, and the values of the query selection expression are determined. At this time, the columns that were narrowed by the external reference column from the search condition compared with = in the subquery are used to create a hash table. (If the predicate is =ANY, =SOME or IN, the selection expression is used to create the hash table.)

Next, an external query is executed, each fetched row is hashed with the value of the external reference column, matched with the hash table that was created from the subquery, and searched. (If the predicate is =ANY, =SOME, or IN, the columns values specified on the left side of the predicate are also used for hashing.)

Figure 4-48 shows the HASH SUBQ processing method.

Figure 4-48 HASH SUBQ processing method

Examples of an EXISTS predicate and a comparison predicate are shown as follows.

Example 1: EXISTS predicate

SELECT T1.C1 FROM T1
  WHERE EXISTS(SELECT * FROM T2 WHERE C1='a' AND C2=T1.C2)

Note

The underlined section is the external reference column.

First, the subquery is evaluated without the condition that includes the external reference column, and a hash table is created from the subquery column (T2.C2) that has been narrowed by using the external reference column. Next, the external query is executed, hashed with the values of the external reference column (T1.C2), and matched with the hash table that was created from the subquery. Then the EXISTS predicate is evaluated.

Example 2: Comparison predicate

SELECT T1.C1 FROM T1
  WHERE T1.C3<(SELECT T2.C3 FROM T2 WHERE C1='a' AND C2=T1.C2)

Note

The underlined section is the external reference column.

First, the subquery is evaluated without the condition that includes the external reference column, and a hash table is created from the subquery column (T2.C2) that has been narrowed by using the external reference column. Next, the external query is executed, hashed with the values of the external reference column (T1.C2), and matched with the hash table created from the subquery. Then the condition that includes the external reference column is evaluated. If the result is true, the comparison predicate (<) is evaluated.