Number of Pages
Min Pages and Max Pages are valid options only when you are modifying the table to hash. These options allow you to control the hashing algorithm to some extent, extending the control offered by the Fillfactor option.
The Min Pages option is useful if the table will be growing rapidly or if you want few rows per page to increase concurrency so multiple people can update the same table.
You can achieve nearly the same effect by specifying a low value for the Fillfactor option, but the fill factor is based on the current size of the table, as described in
Alternate Fill Factors.
To force a specific number of main pages, use the Min Pages option to specify a minimum number of main pages. The number of main pages used are at least as many as specified, although the exact number of Min Pages specified is not used.
Example: Modify Structure and Force a Higher Number of Main Pages for a Table
For example, for the emp table in the previous chapter you can force a higher number of main pages by specifying the minimum number of main pages when you modify the table to hash. If you specify 30 main pages for the table, which has 31 rows, you have approximately one row per page.
To modify the storage structure of the emp table:
modify emp to hash on age with minpages = 30;
In VDBA, open the Structure of Table dialog for the table. Select Hash from the Structure drop-down list. Enter 30 in the Min Pages edit control. Enable the age column in the Columns list.
Example: Specify a Maximum Number of Main Pages for a Table
To specify a maximum number of main pages to use, rather than the system choice, use the Max Pages option. If the number of rows does not completely fit on the number of pages specified, overflow pages are allocated. If fewer pages are needed, the lesser number is used. Max Pages is useful mainly for shrinking compressed hash tables more than otherwise happens.
You can achieve nearly the same effect by specifying a high value for the Fillfactor option, but the fill factor is based on the current size of the table, as described in
Alternate Fill Factors.
The following example modifies the emp table, specifying a Max Pages value.
modify bigtable to hash on empno with maxpages = 100;
In VDBA, open the Structure of Table dialog for the table. Select Hash from the Structure drop-down list. Enter 100 in the Max Pages edit control. Enable the empno column in the Columns list.
Remember that Max Pages controls only the number of main pages; it does not affect overflow pages. For example, assume your data takes 100 pages in heap. If you modify the table to hash and limit the number of main pages to 50, the remainder of the data goes onto overflow pages.