Nonstop Database, HiRDB Version 9 UAP Development Guide
Table 4-13 describes the execution formats and features of inquiries that do not have external references. Table 4-14 describes the optimal execution methods of queries that do not have external references.
Table 4-13 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-14 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) |
Figure 4-47 WORK TABLE ATS SUBQ processing method
SELECT C1 FROM T1 WHERE C2=ANY(SELECT C2 FROM T2)
Figure 4-48 WORK TABLE SUBQ processing method
SELECT T1.C1 FROM T1 WHERE T1.C2=ANY(SELECT C2 FROM T2)
Figure 4-49 ROW VALUE SUBQ processing method
SELECT T1.C1 FROM T1 WHERE T1.C2<(SELECT MAX(C2) FROM T2)
Figure 4-50 HASH SUBQ processing method
SELECT T1.C1 FROM T1 WHERE T1.C2=ANY(SELECT C2 FROM T2)
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.