11. Maintaining Storage Structures : Modify Procedures : Options to the Modify Procedure : Default Fill Factors
 
Share this page                  
Default Fill Factors
Each storage structure has a different default fill factor. The term fill factor refers to the number of rows that are actually put on a data page divided by the number of rows that fit on a data page for a particular structure.
The various fill factors enable you to add data to the table without running into overflow problems. Because the data pages have room to add data, you do not have to remodify.
For instance, a heap table fits as many rows as possible on a page; this is known as 100% fill factor. However, ISAM and B-tree data pages are filled only to 80% capacity, leaving room to add 20% more data before a page is completely full.
The default data page fill factors are as follows:
Storage Structure
Default Fill Factor
Multiply Heap Size by
Number of Pages Needed for 100 Full Pages
B-tree
80%
1.25
125 + index pages
compressed B-tree
100%
1
100 + index pages
hash
50%
2
200
compressed hash
75%
1.34
134
heap
100%
1
100
compressed heap
100%
1
100
ISAM
80%
1.25
125 + index pages
compressed ISAM
100%
1
100 + index pages
The default B-tree index page fill factors are as follows:
Storage Structure
Default Fill Factor
B-tree leaf
70%
B-tree index
80%
The first table shows that if a heap table is 100 pages and you modify that table to hash, the table now takes up 200 pages, because each page is only 50% full.
Note:  Depending on the system allocation for tracking used and free pages, the number of pages can be approximate. For more information, see the chapter “Calculating Disk Space.”