Specifies the amount of space on each cylinder that is to be left unused during the following operations:
|Utility or operation||
The reserved free space allows table data to expand on current table cylinders, preventing or delaying the need for additional table cylinders to be allocated, therefore preventing or delaying data migration operations associated with new cylinder allocations. Keeping new table data physically close to existing table data, and avoiding data migrations, can improve overall system performance.
0 through 75%
Changes Take Effect
After the DBS Control Record has been written, and during the next data load operation. Any operations in progress when the setting is changed are not affected.
Evaluating Free Space Percent Requirements
Reserved free space allows tables to expand within their currently allocated cylinders. This can prevent or delay the need for additional cylinders to be allocated, which incurs the overhead of moving data to the new cylinders. Avoiding new cylinder allocations can improve overall system performance.
- Reference tables that experience no modifications or growth require no FSP, so FSP can be zero for these types of tables. If the system is primarily these types of tables, set FreeSpacePercent to zero, and use the FREESPACE option of the CREATE TABLE and ALTER TABLE SQL statements to set a different FSP value for tables that will experience growth.
- Tables that are expected to experience large scale growth require higher FSP values than tables that grow to lesser degrees. However, larger FSP values consume more storage, so FSP choice should balance space and performance considerations.
A table that would require 100 cylinders of storage with 0% FSP, requires 134 cylinders when FSP is set to 25%. If FSP is 75%, that same table would require 400 cylinders. Ensure that the requisite cylinders are available to satisfy the storage overhead added by the FSP, or performance can suffer.
With time, the available free space may change, due to table modifications that do not honor the FreeSpacePercent setting. The AutoCylPack background task runs periodically to check and restore the FSP for tables. The Ferret PACKDISK command can be run manually to force FSP to be restored, or to temporarily set a table FSP to a different value. MiniCylPack may change the available free space if there is a shortage of cylinders on the system.
|For more information on…||See…|
|CREATE TABLE and ALTER TABLE||Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.|
|Free Space Percent||SHOWFSP, Teradata Vantage™ - Database Administration, B035-1093.|