8. SQL Statements : MODIFY : With Clause Options for Modify : Fillfactor, Minpages, and Maxpages
 
Share this page                  
Fillfactor, Minpages, and Maxpages
FILLFACTOR specifies the percentage (from 1 to 100) of each primary data page that must be filled with rows, under ideal conditions. For example, if you specify a fillfactor of 40, the DBMS Server fills 40% of each of the primary data pages in the restructured table with rows. You can specify this option with the isam, hash, or btree structures. Take care when specifying large fillfactors because a non-uniform distribution of key values can later result in overflow pages and thus degrade access performance for the table.
MINPAGES specifies the minimum number of primary pages a hash table must have. MAXPAGES specifies the maximum number of primary pages a hash table can have. Minpages and maxpages must be at least 1. If both minpages and maxpages are specified in a modify statement, minpages must not exceed maxpages.
For best performance, the values for minpages and maxpages must be a power of 2. If a number other than a power of 2 is chosen, the DBMS Server can change the number to the nearest power of 2 when the modify executes. To ensure that the specified number is not changed, set both minpages and maxpages to that number.
By default, modify to storage-structure resets these attributes back to their defaults (listed below). The modify to reconstruct operation does not affect these attributes.
Default values for fillfactor, minpages and maxpages are listed in this table:
 
 
Fillfactor
Minpages
Maxpages
Hash
50
16
no limit
Compressed hash
75
1
no limit
Isam
80
n/a
n/a
Compressed isam
100
n/a
n/a
Btree
80
n/a
n/a
Compressed btree
100
n/a
n/a