You may wish to modify a hash structure for any of the reasons
you need to modify an ordinary table. In addition, you may
need to recreate the hash structure when it is no longer large
enough for the data in the table.
The SQLMON Static Hash screen
contains information
about each hash structure you have defined.
By examining the OVERFLOW CHAIN LNGTH,
AVGOVERFLOW, and MAXOVERFLOW fields on the Static
Hash screen, you can determine the efficiency of
hash structures.
The AVGOVERFLOW value is the average number of page accesses required
to retrieve a particular row. MAXOVERFLOW is the maximum number of
accesses; therefore, it is a worst case value.
For most hash structures, the normal value of AVGOVERFLOW is between 1.05
and 1.10. This means that approximately 5 to 10% of the primary
pages have one overflow page. A value of 1.5 means that half
the primary pages have one overflow page.
Use this information to decide whether
unloading and reloading the hash structure might improve performance.
To alter the hash structure, simply unload the data, then drop
the table and recreate it with the desired modifications, including,
if necessary, a new number of primary pages. If you add to the number
of primary pages, make sure there are enough empty
TABLE or MIXED DBEFiles in the
appropriate DBEFileSet with the required pages available.
