Calculate Space Requirements for Hash Tables

Share this page

Note: If rows in the table span pages, use the procedure in Calculate Space Requirements When Rows Span Pages (see page Calculate Space Requirements When Rows Span Pages) instead.

Follow these steps to determine the amount of space needed to store the data in a hash table.

1. Create the table and modify it to hash.

2. Determine the number of rows that fit on a page, adjusted for the data page fillfactor to be used.

select tups_per_page * table_dfillpct/100 from iitables where table_name = ‘tablename’;

3. Determine the total number of pages needed for a hash table.

total_hash_pages = (num_rows/(tups_per_page)

Note: Because hashing does not guarantee an equal distribution of rows, the actual number of pages required can be greater than calculated above.