Hitachi

Hitachi Advanced Database Setup and Operation Guide

13.7 Tuning to shorten SQL statement execution time by re-examining the join order of INNER JOINs to which a hash join is applied

This section explains how to shorten SQL statement execution time by re-examining the join order of the INNER JOINs to which a hash join is applied.

If you execute SQL statements that contain INNER JOINs to which a hash join is applied, you might be able to shorten the SQL statement execution time by using a join method specification in which the outer tables and the inner tables of the INNER JOINs are exchanged.

Procedure

  1. Check if there are INNER JOINs to which a hash join is applied.

    Check the executed SQL statement and access path information that is output as SQL trace information. Check if the access path information contains any INNER JOINs to which a hash join was applied.

    If there is an INNER JOIN to which a hash join was applied, go to step 2.

  2. Check whether the hash join resulted in a shortage of space in the hash table area.

    If there is an INNER JOIN to which a hash join was applied, check the access path statistical information that has been output as SQL trace information for that hash join. In the information related to hash table areas that has been output as access path statistical information, check if Y is displayed for the following output item:

    • Hashtbl_area_shortage (whether a space shortage occurred in the hash table area)

    If Y is displayed, go to step 3.

  3. Check the numbers of rows processed in the outer table and the inner table for the hash join.

    If a shortage of hash table area occurred, check the access path statistical information that has been output as SQL trace information. In the information related to retrieval processing that has been output as access path statistical information, check the following output item:

    • Scan_row_cnt (number of rows retrieved)

    Check if the number of rows processed in the outer table for the hash join is much larger than the number of rows processed in the inner table. If the former is much larger than the latter, go to step 4.

  4. Re-examine the join order of the hash joins.

    In the INNER JOINs to which a hash join is applied, specify the join method specification in such a manner that the inner table whose processed row count was smaller becomes the outer table.

    If the number of rows processed in the outer table for a hash join is much larger than the number of rows processed in the inner table, the data in the outer table that could not be processed in the hash table area was stored temporarily in a work table. This might be the cause of the long SQL statement execution time.

    By using the join method specification, you might be able to shorten the execution time of SQL statements that contain INNER JOINs to which a hash join is applied.

Note

To Page Top

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