Hitachi

Hitachi Advanced Database Application Development Guide

5.6.3 Methods for processing subqueries that contain an external reference column

There are three methods for processing subqueries that contain an external reference column:

When using the nested loops row value execution method, cache area is sometimes created to store the results of the subquery. This is to reduce the number of times the subquery is executed.

These processing methods are explained below.

Organization of this subsection

(1) Nested loops work table execution

Subquery processing might be performed with nested loops work table execution applied in the following cases:

The following shows an example of nested loops work table execution.

■ SELECT statement to be executed
SELECT "T1"."C1" FROM "T1"
    WHERE "T1"."C1"=ANY(SELECT "T2"."C1" FROM "T2"
                            WHERE "T2"."C2"="T1"."C2")
Figure 5‒12: Processing method for nested loops work table execution

[Figure]

Explanation:

  1. Executes the query that is outside the subquery

    This example retrieves table T1.

  2. Executes the subquery by using the value of an external reference column each time one row of query outside the subquery is fetched.

    This example retrieves table T2 by using the value of an external reference column ("T1"."C2") as the condition value for each row of table T1 retrieval results.

  3. Creates a work table based on the result of the executed subquery.

    This example retrieves table T2 and stores the value of "T2"."C1" in the work table.

  4. Uses the created work table to evaluate the condition that contains a subquery outside the subquery.

    This example evaluates the condition containing the subquery by matching with the value of corresponding "T2"."C1" in the work table for each row of table T1 retrieval results.

To Page Top

(2) Nested loops row value execution

Subquery processing might be performed with nested loops row value execution applied in the following cases:

The following shows an example of nested loops row value execution.

■ SELECT statement to be executed
SELECT "T1"."C1" FROM "T1"
    WHERE "T1"."C1"=(SELECT MAX("T2"."C1") FROM "T2"
                         WHERE "T2"."C2"="T1"."C2")
Figure 5‒13: Processing method for nested loops row value execution

[Figure]

Explanation:

  1. Executes the query that is outside the subquery.

    This example retrieves table T1.

  2. Executes the subquery by using the value of an external reference column each time one row of query outside the subquery is fetched.

    This example obtains the result of subquery MAX("T2"."C1") by using an external reference column ("T1"."C2") for each row of table T1 retrieval results.

  3. Obtains the results of the executed subquery (no work table is created). HADB then uses the results of the subquery to evaluate the condition that contains a subquery outside the subquery.

    This example evaluates the condition by using the value of the corresponding MAX("T2"."C1") for each row of table T1 retrieval results.

To Page Top

(3) Hash execution

A method for processing subqueries by using a hash table is called hash execution. Hash execution might be applied in the following cases:

If hash execution is applied during subquery processing, HADB first executes the subquery from which a condition containing the external reference column is excluded, and then creates a hash table from the result. Then, HADB executes the query outside the subquery, and then generates a hash value from the value of the external reference column. Finally, processing is performed to match the values with the hash table.

When a hash table is created, a hash filter is also created. HADB filters hash values by using the hash filter before matching the hash values with the hash table. This reduces the number of times hash values are matched with the hash table.

The following shows an example of hash execution.

■ SELECT statement to be executed
SELECT "T1"."C1" FROM "T1"
   WHERE "T1"."C3"<(SELECT "T2"."C3" FROM "T2"
                        WHERE "T2"."C1"='A' AND "T2"."C2"="T1"."C2")
Figure 5‒14: Processing method for hash execution

[Figure]

Explanation:

  1. Executes the subquery from which a condition containing the external reference column (underlined portion in the example SQL statement) is excluded, and then creates a hash table and hash filter based on the result.

  2. Executes the query outside the subquery, and then generates a hash value from the value of the external reference column ("T1"."C2"). The hash value is checked by using the hash filter. This example retrieves table T1, generates a hash value from the value of the external reference column ("T1"."C2"), and then checks the hash value by using the hash filter.

  3. Performs processing to match the hash table with the hash values that have passed the hash filter.

The range index might be used when a hash execution is processed if both of the following two conditions are met:

If both of the preceding two conditions are met, the maximum and minimum values of the column to be compared with an external reference column are obtained when: a hash table is created from the result of executing a subquery excluding the conditions that contain the external reference column during processing of a hash execution. The range index is then used when the table specified in a query outside the subquery (table T1 in the preceding example) is searched. The range index is used to skip the table's chunks or segments that are not within the obtained maximum and minimum values of the column to be compared with the external reference column.

A hash table is created in the hash table area. The size of the hash table area is specified in the adb_sql_exe_hashtbl_area_size operand in the server definition or the client definition. Note that when 0 is specified in the adb_sql_exe_hashtbl_area_size operand, hash execution is not applied.

A hash filter is created in the hash filter area. The size of the hash filter area is specified in the adb_sql_exe_hashflt_area_size operand in the server definition or the client definition. If 0 is specified for the adb_sql_exe_hashflt_area_size operand, a hash filter is not applied during hash execution.

■ Flow of processing when hash table area has insufficient space

The following explains the flow of processing when the hash table area has insufficient space to create the hash table.

Note that tables T1 and T2 in the following explanation correspond to tables T1 and T2 in Figure 5‒14: Processing method for hash execution.

  1. If there is insufficient hash table area to create the hash table, the data of table T2 is stored in multiple work tables. Also, the data of table T1 is stored in multiple work tables in the same way as for the data of table T2.

    [Figure]

    Note

    In the preceding example, three work tables are created for table T2, and three work tables are created for table T1. The number of work tables that are created differs depending on conditions, such as the specification of the SQL statement.

  2. A hash table is created with a work table for table T2 (work table T2-1), and then the hash table is matched with a work table for table T1 (work table T1-1).

    After matching between the hash table and work table T1-1 is complete, a hash table is created with work table T2-2, and the hash table is matched with work table T1-2.

    After matching between the hash table and work table T1-2 is complete, a hash table is created with work table T2-3, and the hash table is matched with work table T1-3.

    [Figure]

If the hash table area becomes insufficient during the processing in step 2

If the hash table area becomes insufficient during the processing in step 2, any data that cannot fit in the hash table is stored in another work table. In this case, in addition to matching between a work table for table T1 and a hash table, matching between a work table for table T1 and the newly created work table (work table T2-4) occurs.

[Figure]

Note

If a new work table (work table T2-4) is created due to insufficient hash table area during the processing in step 2, the KFAA51130-W message is output to the server message log file.

■ Action to take when the hash table area has insufficient space

If the hash table area is insufficient, processing time for the SQL statement might take longer due to work table creation and matching. To remedy a situation in which the hash table area has insufficient space, increase the value specified for the adb_sql_exe_hashtbl_area_size operand in the server definition or the client definition. This operand specifies the size of the hash table area.

■ Action to take when the hash filter area has insufficient space

If the size of the hash filter area specified for the adb_sql_exe_hashflt_area_size operand in the server definition or client definition is too small, a shortage might occur in the hash filter area allocated for each hash retrieval. As a result, a hash filter is not applied to any hash retrieval for which the size of the hash filter is insufficient. If you want to apply the hash filter to all types of hash retrieval, increase the value of the adb_sql_exe_hashflt_area_size operand. For details about the formula for estimating the value to be specified, see (6) Conditions where a hash filter is applied in 5.5.2 About hash join.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.