You can alter existing tables to take advantage of hash
access organization and improve the performance of queries that access
individual rows in a table.
Before you begin
Hash organization is only available on universal table spaces
(UTS). If you want to enable hash access on a table space that is
not already a UTS, you must first alter the table space to UTS.
About this task
Enabling hash access requires a table space reorganization,
and disables some features such as index clustering.
Procedure
To alter an existing table to take advantage of hash
organization:
- Specify ADD ORGANIZE BY HASH in the organization-clause
of your ALTER TABLE statement.
- Specify UNIQUE followed by the column names for one
or more columns that contain unique values in each row. You
can specify more than one column-name as long as no two rows in the
table have the same values in those columns. You can only specify
columns that are defined as NOT NULL. You can specify a maximum of
64 columns to be used as unique identifiers for hash access. The sum
of the column length attributes must not exceed 255. DB2® maintains the uniqueness of the hash key columns, and an index is not needed for this purpose.
- Specify HASH SPACE followed by an integer and a modifier
that specifies the size of the hash space. You can specify
the size of the hash space in kilobytes, megabytes, and gigabytes.
Specify:
- K for kilobytes
- M for megabytes
- G for gigabytes
You can specify a size that is larger than your data to minimize
the overhead of access to data that overflows the hash space. The
size that you specify is most important if you do not intend to immediately
reorganize the table space and specify the AUTOESTSPACE(YES) option,
as is recommended below. In that case, for more information about
choosing an appropriate size for the hash space, see Fine-tuning hash space and page size.
- Commit the ALTER TABLE statement.
- Run the REORG TABLESPACE utility on the table space where
your altered table is located. If you specify AUTOESTSPACE(YES)
in the REORG TABLESPACE statement,DB2 automatically
estimates the best size for the hash space based on information from
the real-time statistics tables. If you specify AUTOESTSPACE(NO) in
the REORG TABLESPACE statement, DB2 uses
the hash space that you specified.
Example
Consider the following ALTER
TABLE statement:
ALTER TABLE EMP
ADD ORGANIZE BY HASH UNIQUE (EMPNO)
HASH SPACE 64 M;
In
this example the user alters the EMP table,
specifies to ADD ORGANIZE BY HASH, sets the EMPNO column as the unique
identifier, and specifies a HASH SPACE of 64 with
the modifier M for megabytes.
What to do next
Monitor the real-time-statistics information about your table
to verify that the hash access path is used regularly and to verify
that the use of disk space is optimized.