FreeSpacePercent - Teradata Database

FreeSpacePercent

Purpose  

Specifies the amount of space on each cylinder that is to be left unused during the following operations:

Type	Operations
Utility	FastLoad and MultiLoad Archive/Recovery RESTORE Table Rebuild Reconfiguration Ferret PACKDISK MiniCylPack operations attempt to honor the FreeSpacePercent (FSP) setting, or the FSP value specified in CREATE TABLE and ALTER TABLE statements. However, if few cylinders are available, and storage space is limiting, MiniCylPack may not be able to honor that FSP.
SQL	An ALTER TABLE that adds fallback protection. A CREATE INDEX that defines or redefines any type of secondary index on a populated table. Fallback creation during an INSERT...SELECT into an empty table that is defined with fallback protection. Index creation during an INSERT...SELECT into an empty table that is defined with any type of secondary index.

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.

Field Group

File System

Valid Range

0 through 75%

Default

0%

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.

Note: After setting a non-zero value for the free space percentage, all subsequent operations listed above will respect that setting, and will continue to reserve free space beyond what table data requires. To have Teradata Database utilize the reserved free space for data storage and avoid data migrations, the free space percentage must be reduced after the initial data is loaded.

Evaluating FSP 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.

Choosing an appropriate FSP value for a table involves both performance and space considerations, and depends on the growth needs of the table:

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.

Related Topics

For more information on…	See…
CREATE TABLE and ALTER TABLE	SQL Data Definition Language.
Free Space Percent	“FreeSpacePercent” on page 341, “SHOWFSP” on page 611, Database Administration.
AutoCylPack	“DisableAutoCylPack” on page 305.
MiniCylPack	“MiniCylPackLowCylProd” on page 384.