When you design your databases you must take care to specify appropriate free space to allow for data growth.
Free space, sometimes called fill factor, is a DDL option that can be specified when creating or altering your table spaces and indexes. It is used to leave a portion of a the database obect empty and available to store newly added data. The specification of free space in a table space or index can reduce the frequency of reorganization, minimize contention, and improve the efficiency of inserting new data.
Each DBMS provides a method of specifying free space for a database object in the CREATE and ALTER statements. A typical parameter is PCTFREE, where the DBA specifies the percentage of each data page that should remain available for future inserts. Another possible parameter is FREEPAGE, where the DBA indicates the specified number of pages after which a completely empty page is available.
Ensuring a proper amount of free space for each database object provides the following benefits:
- Inserts are faster when free space is available.
- As new rows are inserted, they can be properly clustered.
- Variable-length rows and altered rows have room to expand, potentially reducing the number of relocated rows.
- Having fewer rows on a page results in better concurrency because less data is unavailable to other users when a page is locked.
However, free space also has several disadvantages:
- Disk storage requirements are greater.
- Scans take longer.
- Fewer rows on a page can necessitate more I/O operations to access the requested information.
- Because the number of rows per page decreases, the efficiency of data caching can decrease because fewer rows are retrieved per I/O.
The DBA should monitor free space and ensure that the appropriate amount is defined for each database object. The correct amount of free space must be based on:
- The expected frequency of inserts and modifications
- Amount of sequential versus random access
- Impact of accessing unclustered data
- Type of processing
- Likelihood of row chaining, row migration, and page splits
Be careful, though, not to fall prey to a common database design mistake, namely specifying the same default amount of free space for every object. I’ve seen many database implementations where 20% (or 10%) free space has been specified for every object. This is surely not a good idea. Why?
Well, not every table grows at the same rate. A transaction history table will likely grow rapidly, but perhaps all new transactions are added to the end. Free space is not needed for such a table because your rarely, if ever, go back and edit old transactions. A customer table, hopefully grows over time. Perhaps the data is clustered by Customer Name, in which case some free space will be helpful.
Finally, consider static tables. For example, a code table or maybe a table that contains the 50 state codes with the state name. This type of table rarely changes, so do not define any free space for it because it does not need room in which to expand.
Armed with appropriate knowledge of your applications and data you can define reasonable free space into your database objects to allow for data growth with minimal impact on performance. Be sure to understand how your DBMS enables free space and define it accordingly for your production databases.