Table 4-11 describes the execution formats and features of inquiries that do not have external references. Table 4-12 describes the optimal execution methods of queries that do not have external references.

Table 4-11 Execution methods and features of subqueries with no external references

Execution method	Processing method	Advantages	Disadvantages
Work table ATS execution	This method obtains the subquery results beforehand and creates a work table. Then when a search using an index is conducted for an external query, this method uses the work table that was created from the subquery results to narrow the search range.	An index can be used for an external query. Therefore, when the number of subquery hits is small and the number of external queries is large, data can be searched rapidly when an index is used to narrow the search range.	When the number of subquery hits is large, the performance drops because a search using an index for the external query must be performed for each row in the subquery results.
Work table execution	This method obtains the subquery results beforehand and creates a work table. Then each time a row of the external query is searched, this method matches the row with the work table that was created from the subquery results and evaluates the predicate that contains the subquery.	This method can be applied to all subquery conditions that require a work table.	The performance drops when the number of external queries is large.
Row value execution	This method obtains the subquery beforehand. (A work table is not created.) Then, when an external query is searched, this method uses the values of the subquery results to evaluate the condition that includes the subquery.	An index can be used for external queries. Therefore, if the number of external queries is large, an index can be used to narrow the search range, and data can be searched rapidly.	The performance drops when the number of external queries is high and the predicates that include subqueries cannot be narrowed using an index.
Hash execution	This method creates a hash table from the subquery results beforehand. Then each time a row of the external query is retrieved, this method hashes the external query value and evaluates the condition that includes the subquery.	Data can be searched rapidly when the number of subquery hits is small and the number of external queries is large.	If the number of subquery hits is large, the work table buffer size to be 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.

Table 4-12 Optimal execution method of subqueries with no external references

Subquery	Optimal execution method
Table subqueries specified on the right side of the =ANY and =SOME quantified predicates and the IN predicate	The method differs depending on the number of data items in the external query or subquery.
	External queries: Many Subqueries: Few	Work table ATS execution or hash execution is effective.
	External queries: Intermediate Subqueries: Few
	External queries: Few Subqueries: Few
	External queries: Many Subqueries: Intermediate	Hash execution is effective
	External queries: Intermediate Subqueries: Intermediate
	External queries: Few Subqueries: Intermediate	Hash execution or work table execution is effective.
	External queries: Many Subqueries: Many	Hash execution is effective. (Performance improvement cannot be executed because the number of data items is high.)
	External queries: Intermediate Subqueries: Many
	External queries: Few Subqueries: Many	Hash execution or work table execution is effective. If the predicate is converted to an EXISTS predicate that contains an external reference, HiRDB may be able to conduct the search rapidly.
Table subqueries specified on the right side of quantified predicates (except =ANY and =SOME) and the IN predicate	Work table execution is always applied.
Subqueries of the EXISTS predicate	Row value execution is always applied.
Other subqueries (scalar subqueries and row subqueries)

(a) Work table ATS execution

WORK TABLE ATS SUBQ

This processing method applies to table subqueries specified on the right side of =ANY and =SOME quantified predicates and IN predicates.

First, HiRDB calculates the values of the subquery selection expression and creates a work table. Next, HiRDB uses an index to retrieve external queries. To retrieve the queries, HiRDB uses the subquery results to narrow the index search range. The query search conditions are ATS and RANGES.

In some cases, HiRDB executes duplicate elimination (DISTINCT) internally for subqueries. Figure 4-42 shows the WORK TABLE ATS SUBQ processing method.

Figure 4-42 WORK TABLE ATS SUBQ processing method

An example of a quantified predicate and a comparison predicate is shown as follows.

Example

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

Note

This example supposes that an index is defined in T1 (C2).

First, table T2 of the subquery is searched, and a work table is created from the values of T2.C2. Next, the values of T2.C2 are fetched one row at a time from the work table, and a search is conducted by narrowing the search range of the index defined in T1.C2 of the external query.

(b) Work table execution

WORK TABLE SUBQ

This processing method is applied to table subqueries specified on the right side of quantified predicates and IN predicates. First, the values of the subquery selection expression are determined and a work table is created. Next, the outer query is searched. Each time a row of the outer query is searched, the row is matched with the results of the subquery, and the search conditions are evaluated.

Figure 4-43 shows the WORK TABLE SUBQ processing method

Figure 4-43 WORK TABLE SUBQ processing method

Example

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

First, table T2 of the subquery is searched, and a work table is created from the values of T2.C2. Next, the outer query is executed, the rows are fetched one at a time, the T1.C2 values are matched with the work table that was created from the subquery, and the search conditions are evaluated.

(c) Row value execution

ROW VALUE SUBQ

This processing method is applied to row subqueries, scalar subqueries, and EXISTS predicates. With this method, first the value of the selection expression in the subquery is determined. Then, the value of the subquery result is used in evaluating the conditions, including the subquery of the outside query.

With comparison predicates, if HiRDB judges that using an index is better when searching an external query, it uses an index in the search.

Figure 4-44 shows the ROW VALUE SUBQ processing method.

Figure 4-44 ROW VALUE SUBQ processing method

An example is shown as follows.

Example

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

First, table T2 of the subquery is searched, and the MAX(T2.C2) values are fetched. (A work table is not created.) Next, the condition that includes the subquery in the external query is evaluated with the MAX(T2.C2) values.

(d) Hash execution

HASH SUBQ

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

First, the values of the subquery selection expression are determined, and a hash table is created from the selection expression values. Next, the external query is executed, hashed with the column values specified on the left side of the quantified predicate and IN predicate, matched with the hash table that was created from the subquery, and searched.

Figure 4-45 shows the HASH SUBQ processing method.

Figure 4-45 HASH SUBQ processing method

An example is shown as follows.

Example

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

First, table T2 of the subquery is searched, and a hash table is created from the T2.C2 values. Next, the external query is executed, hashed with the T1.C2 values, matched with the hash table that was created from the subquery, and searched.