Organizing tables for hash access to individual rows (deprecated)

You can organize tables for hash access to enable queries that use unique equal predicates to access individual rows. Hash-organized table spaces are deprecated and likely to be unsupported in the future.

About this task

Deprecated function:

Hash-organized tables are deprecated. Beginning in Db2 12, packages that are bound with APPLCOMPAT(V12R1M504) or higher cannot create hash-organized tables or alter existing tables to use hash-organization. Existing hash-organized tables remain supported, but they are likely to be unsupported in the future.

When you create a table or alter an existing table, you can choose to organize the table by hash. You can also alter the size of the hash space on tables that are already organized by hash.

Tables that are organized by hash are efficient for access by queries that use equal predicates that are unique to individual rows in the table. When Db2 selects a hash access path, as few as one I/O might be required to retrieve the row from the table, which reduces CPU usage. However, hash access requires additional disk space to store data.

Tables that are good candidates for hash organization have the following attributes:

  • Db2 uses unique value lookup in an index to access rows in the table, or the table is in memory. When you organize a table for hash access, Db2 automatically enforces uniqueness on a column or set of columns that you specify. So, you do not need to enforce uniqueness separately.
  • Db2 does not use range scans to access rows in the table.
  • Db2 uses random lookups to access data in the table.
  • The table is most often used for queries that are satisfied by a row in the table, more often than not.
  • The size of the data in the table is relatively static, or the maximum size of the data is known.
  • The size of individual rows in the table do not vary greatly.
  • At least 20 rows fit on a data page.

Tables that have a stable or predictable size are good candidates for hash organization. Tables that are smaller than the specified hash space do not use their allocated disk space efficiently. Db2 automatically creates an overflow index when you organize a table by hash. If a table exceeds the specified hash space, Db2 places extra rows in the overflow index. Rows that are placed in this index are not enabled for hash access, and Db2 uses the index to retrieve them.

The following restrictions apply to tables that are organized by hash:

  • The following types of tables and table spaces cannot be organized for hash access:
    • LOB table spaces
    • XML table spaces
    • MQT table spaces
    • Tables that have clone tables
  • Clustered indexes are not compatible with tables that are organized for hash access.
  • Hash access is not used to access any table in a query block that contains a star join (JOIN_TYPE = S)
  • Hash access is not used for sensitive dynamic scrollable cursor with multi-element in-list.
  • Parallelism is not used for parallel groups when hash access is used.

Scrollable cursors are available on the result table of a query with an IN list predicate.

You can organize tables by hash only if the tables are in partition-by-growth or partition-by-range tables spaces, and they use reordered row format. Tables in basic row format cannot to take advantage of hash organization.

Fine-tuning hash space and page size

You can fine-tune the amount of overhead from hash overflow by adjusting two attributes of the hash-organized table space: The amount of additional space, and the number of rows per data page.

Before you begin

Begin general-use programming interface information.Before trying to manually tune the size of your hash space, determine whether automatic space estimation yields acceptable performance:
  1. Reorganize the hash-organized table space and specify the AUTOESTSPACE(YES) option.
  2. Monitoring hash access (deprecated)

About this task

The process that Db2 uses to determine the placement of a row in a hash-organized table space is somewhat, but not completely, random. Consequently, statistical variation means that sometimes too many rows are placed on the same page When that happens, rows that do not fit on the page are stored outside of the hash space, and an entry is added to a hash overflow index so that the row can be located. When that happens an overflow occurs. Rows that do not fit on the determined page are stored outside of the hash space, and an entry is added to a hash overflow index so that the row can be located.

By allocating additional space for table space, you can reduce the number of these overflow situations. The number of rows that fit on a page also has a significant impact on the amount of overflows.

The following table shows estimated multipliers for determining the amount of additional space to allocate, according to the number of rows that fit on page, to achieve a certain estimated percentage of overflows.

Table 1. Estimated size multipliers for fixed-size hash-organized table spaces by percentage of overflows.
Rows per page 20% overflow 10% overflow 5% overflow 2.5% overflow 1.0% overflow 0.5% overflow 0.1% overflow 0.01% overflow 0.0001% overflow
1 2.18 4.69 9.69 19.7 49.7 99.7 499.7 4999.7 49999.7
2 1.3 2.08 3.16 4.68 7.69 11.07 25.35 81.18 257.73
3 1.1 1.6 2.2 2.95 4.23 5.49 9.82 21.83 47.7
4 1.02 1.4 1.83 2.33 3.13 3.87 6.13 11.43 20.85
5 0.97 1.29 1.64 2.02 2.61 3.12 4.61 7.75 12.71
7 0.92 1.18 1.44 1.71 2.1 2.43 3.31 4.94 7.19
10 0.88 1.1 1.3 1.5 1.77 1.99 2.54 3.47 4.63
15 0.86 1.04 1.19 1.34 1.54 1.69 2.04 2.59 3.22
20 0.85 1.01 1.14 1.27 1.42 1.54 1.81 2.21 2.64
30 0.84 0.98 1.09 1.19 1.31 1.39 1.59 1.86 2.14
40 0.83 0.96 1.06 1.15 1.25 1.32 1.47 1.69 1.9
50 0.83 0.95 1.04 1.12 1.21 1.27 1.41 1.58 1.76
100 0.83 0.94 1.01 1.07 1.13 1.17 1.26 1.37 1.47
150 0.83 0.93 1 1.05 1.1 1.13 1.2 1.29 1.36
200 0.83 0.93 0.99 1.04 1.08 1.11 1.17 1.24 1.31
250 0.83 0.93 0.99 1.03 1.07 1.1 1.15 1.22 1.27

Procedure

To fine tune the size of your hash space:

  • Ensure that a sufficient amount of additional space is provided.
    For example, if 20 rows fit on a data page, and you want approximately 5% of the rows to be hash overflows, you would multiply the size of your data by 1.14 (14% overhead) to determine the size of your hash space. Note that additional space is actually used in that case because 5% of the rows overflow, meaning a total space overhead of 19%
  • Ensure that an appropriate number of rows fit on each page.
    For example, a table that contains 1500-byte rows in 4KB pages has two rows on each page. To achieve a overflow level of 5% would require you to specify a fixed hash space size that is 3.16 times larger than the size of the data in the table. However, if you convert the table to use 32KB pages, each page would contain 16 rows, meaning that a fixed size for the hash space between 1.14 and 1.19 times larger would be sufficient to achieve 5% overflows.End general-use programming interface information.

Monitoring hash access

You can optimize the performance of hash access in Db2 by monitoring and tuning the storage space that is used by hash access.

About this task

The SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS tables contain indicators that can help you optimize hash access and improve single-row access for queries that use equal predicates.

Procedure

To optimize the use of hash space and improve single-row access on tables that are organized by hash:

  • Compare the values for SYSINDEXSPACESTATS.TOTALENTRIES and SYSTABLESPACESTATS.TOTALROWS.
    If the TOTALENTRIES value is greater than 10 or 15 percent of the value of TOTALROWS, the data might need to be reorganized, or the size of the hash space might need to be increased. However, careful analysis of the row size, page size, and PCTFREE values might be needed to identify when to reorganize a particular hash-organized table. For more information about selecting an appropriate size for the hash space, see Managing space and page size for hash-organized tables .
  • Compare the values for SYSTABLESPACESTATS.DATASIZE and SYSTABLESPACE.HASHSPACE.
    If DATASIZE is greater than HASHSPACE, increase the size of the hash space. The recommended size for hash spaces depends on the row size and page size. For more information about choosing a size of the hash space, see Managing space and page size for hash-organized tables.
  • Monitor SYSTABLESPACESTATS.REORGHASHACCESS to ensure that applications are using hash access paths on tables that are organized by hash.
    This value represents the number of times that a hash home page is accessed to locate a record for operations such as the following, regardless of whether a qualifying record is found:
    • SELECT
    • FETCH
    • Searched UPDATE
    • Searched DELETE
    • Enforcement of referential integrity constraints

    For partition-by-growth table spaces, the value is only increased for partitions that contain hash home pages. This value is always zero for partitions in a partition-by-growth table space that do not have hash home pages, even though pages in these partitions might be accessed through the hash overflow index.

    If the REORGHASHACCESS value does not increase after several queries have accessed the table, the hash access path is not being used regularly. Consider removing hash organization from such tables to conserve storage space. The value of REORGHASHACCESS resets to zero after the REORG TABLESPACE or LOAD REPLACE utility is run.

  • Monitor SYSTABLESPACESTATS.HASHLASTUSED to ensure that applications have used hash access paths on the table recently. If the Db2 has not recently used a hash access path on the table, consider removing hash organization from the table to conserve storage space.