Hitachi

Hitachi Advanced Database Application Development Guide

5.6.1 Methods for processing subqueries that do not contain an external reference column

There are four methods for processing subqueries that do not contain an external reference column:

These processing methods are explained below.

Organization of this subsection

(1) Work table execution

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

The following shows an example of work table execution.

■ SELECT statement to be executed
SELECT "T1"."C1" FROM "T1"
    WHERE ABS("T1"."C2")=ANY(SELECT "T2"."C2" FROM "T2")

This example assumes that no B-tree index or text index is defined for column C2 in table T1.

Figure 5‒8: Processing method for work table execution

[Figure]

Explanation:

  1. Stores the result of the subquery in the work table.

    This example searches table T2 specified in the subquery, and then stores the value of column C2 of table T2 in the work table.

  2. Executes the query that is outside the subquery. Each time HADB retrieves one row of query outside the subquery, HADB matches that row with the result of the subquery (in the work table) and evaluates the search condition.

    This example retrieves one row of table T1 at a time, and then matches the absolute value of column C2 of table T1 with the value of column C2 of table T2 stored in the work table to evaluate the search condition.

To Page Top

(2) Row value execution

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

The following shows an example of row value execution.

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

[Figure]

Explanation:

  1. Obtains the result of the subquery.

    This example searches table T2 specified in the subquery, and then obtains MAX("T2"."C2").

  2. Uses the result of the subquery to evaluate the condition that contains a subquery of the query outside the subquery. For a comparison predicate, B-tree indexes or text indexes might be used to execute the query outside the subquery.

    This example retrieves table T1 using MAX("T2"."C2") obtained in 1 as the condition value. Depending on the condition, B-tree indexes or text indexes might be used for the retrieval.

To Page Top

(3) Work table row value execution

Subquery processing might be performed with work table row value execution applied in the following cases:

The following shows an example of work table row value execution.

■ SELECT statement to be executed
SELECT "T1"."C1" FROM "T1"
    WHERE "T1"."C2"=ANY(SELECT "T2"."C2" FROM "T2")

This example assumes that B-tree indexes are defined for column C2 of table T1.

Figure 5‒10: Processing method for work table row value execution

[Figure]

Explanation:

  1. Stores the result of the subquery in the work table.

    This example searches table T2 specified in the subquery, and then stores the value of column C2 of table T2 in the work table.

  2. Fetches one row value from the work table at a time and executes the query outside the subquery to evaluate the search condition. A B-tree index is used for this processing. A text index is used if one is defined.

    This example fetches the value of column C2 of table T2 from the work table one row at a time and retrieves table T1 by using the B-tree index defined for the column C2 of table T1.

To Page Top

(4) 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 creates a hash table on the basis of the result of the subquery. Then, HADB executes the query outside the subquery, and then generates hash values from the value of the column specified to the left of the quantified predicate or IN predicate. 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"."C2"=ANY(SELECT "T2"."C2" FROM "T2")
Figure 5‒11: Processing method for hash execution

[Figure]

Explanation:

  1. Creates a hash table and hash filter on the basis of the result of the subquery (underlined portion in the example SQL statement). This example retrieves table T2 specified in the subquery, and then creates a hash table and hash filter from the value of column C2 of table T2.

  2. Executes the query outside the subquery, and then generates a hash value from the value of the column specified to the left of the quantified predicate (in the example SQL statement, column C2 of table T1). The hash value is checked by using the hash filter. This example fetches one row from table T1 at a time, generates the hash value from the value of column C2 of table T1, 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:

  • There is a table specified in a query outside a subquery (table T1 in the preceding example). In the table, the range index is defined for the column specified to the left of the quantified predicate or IN predicate (column T1.C2 in the preceding example).

  • The conditions under which the range index can be used are met.

    For details about the conditions under which range indexes are used, see 5.3.1 Conditions under which range indexes are used during execution of an SQL statement.

If both of the preceding two conditions are met, when a hash table is created from the subquery result during processing of a hash execution, the maximum and minimum values of the subquery are obtained. 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 subquery result.

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 the tables T1 and T2 in the following explanation correspond to tables T1 and T2 in Figure 5‒11: 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.