Hitachi

Hitachi Advanced Database Application Development Guide


5.9.1 Hash execution

If hash execution is used as the method for processing SELECT DISTINCT, HADB performs deduplication while creating a hash table by hashing retrieval results.

The following shows an example of hash execution.

■ SELECT statement to be executed
SELECT DISTINCT "C1" FROM "T1"
Figure 5‒19: Processing method for hash execution

[Figure]

Explanation:

  1. Retrieves table T1, and then extracts the value of column C1 of table T1.

  2. Performs deduplication while creating a hash table by hashing the result of step 1.

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.

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

When the hash table area has insufficient space, the data stored in the hash table is spread over multiple work tables. This results in SQL statements taking longer to process. 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.

When a work table is created as a result of insufficient hash table area, the message KFAA51130-W is output to the server message log file.

■ 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.

  1. If there is insufficient space in the hash table area to create the hash table, HADB creates multiple work tables. The data to be stored in the hash table is spread across these work tables.

    [Figure]

  2. A hash table is created for each work table.

    [Figure]

    If the hash table area runs out of space when creating hash tables for work tables, HADB creates a new work table. HADB stores in this work table the data that did not fit in the hash table area, and then performs deduplication.

    [Figure]