Alternate Fill Factors
You can tailor the fill factor for various situations. For instance, if the table is not going to grow at all, use a 100% fill factor for the table. On the other hand, if you know you are going to be adding a lot of data, you can use a low fill factor, perhaps 25%. Also, if your environment is one where updates are occurring all the time and good concurrency is important, you can set the fill factor low.
Note: Fill factor is used only at modify time. As you add data, the pages fill up and the fill factor no longer applies.
When specifying a fill factor other than the default, you must keep the following points in mind:
• Use a high fill factor when the table is static and you are not going to be appending many rows.
• Use a low fill factor when the table is going to be growing rapidly. Also, use a low fill factor to reduce locking contention and improve concurrency. A low fill factor distributes fewer keys per page, so that page level locks lock fewer records.
Specifying fill factor is useful for hash and ISAM tables. However, for B-tree tables, because data pages only are affected, the Fillfactor option must be used with the Leaffill or Nonleaffill options. See
Leaf Page Fill Factors (see page
Leaf Page Fill Factors) and
Index Page Fill Factors (see page
Index Page Fill Factors).
For hash tables, typically a 50% fill factor is used for uncompressed tables. You can raise or lower this, but raising it too high can cause more overflow pages than desirable. You must always measure the overflow in a hash table when setting a high fill factor--fill factors higher than 90% are likely to cause overflow.
If you are using compressed ISAM tables and are adding data, make sure you set the fill factor to something lower than the default 100%, or you immediately add overflow pages.
Normally, uncompressed ISAM tables are built with an 80% fill factor. You can set the fill factor on ISAM tables to 100%, and unless you have duplicate keys, you cannot have overflow problems until after you add data to the table.
In VDBA, you control the fill factor of the data pages using the Fillfactor option in the Structure of Table and Structure of Index dialogs.
Example: Set Fill Factor to 25% on a Hash Table
This example sets the fill factor on a hash table to 25%, rather than the default of 50%, by modifying the emp table:
modify emp to hash on empno with fillfactor = 25;
In VDBA:
1. Open the Structure of Table dialog for the table.
2. Select Hash from the Structure drop-down list.
3. Enter 25 in the Fillfactor edit control.
4. Enable the empno column in the Columns list.
Example: Set Fill Factor to 100% on an Uncompressed ISAM Table
This example sets the fill factor on an uncompressed ISAM table to 100%:
modify employee to isam on name with fillfactor = 100;
In VDBA:
1. Open the Structure of Table dialog for the table.
2. Select Isam from the Structure drop-down list.
3. Enter 100 in the Fillfactor edit control.
4. Enable the name column in the Columns list.