Use the data in the following table to determine a value for FSP.
| IF the majority of tables are… | THEN… |
|---|---|
| read-only | set the default system-wide FSP value to 0. You can override the FSP for the remaining modifiable tables on an individual table basis with the ALTER TABLE statement. |
| NOT read-only | the FSP value depends on the percentage of increase in table size due to the added rows. Set the FreeSpacePercent parameter to reflect the net growth rate of the data tables (inserts minus deletes). Common settings are 5 to 15%. A value of 0% is appropriate for tables that are not expected to grow after initially being loaded. For example, if the system keeps a history for 13 weeks, and adds data daily before purging a trailing 14th week, use an FSP of at least 1/13 (8%). To accommodate minor data skews and any increase in weekly volume, you can add an extra 5% (for a total of 13%) FSP. |
Because the system dynamically allocates free cylinder space for storage of new or updated rows, leaving space for this during the initial load allows a table to expand with minimal cylinder splits and migrates. However, if you do not expect table expansion, that is, the majority of tables are read-only, use the lowest value (0%) for FSP.
If the system default FSP is zero, do as shown in the following table to minimize problems.
| IF you… | THEN… |
|---|---|
| use read-only tables | change nothing. At load time, or PACKDISK time, the system stores tables at maximum density. |
| add data via BTEQ or a CLI program | set the FREESPACE value on the CREATE TABLE statement to an appropriate value before loading the table. If the table is loaded, use the ALTER TABLE statement to change FREESPACE to an appropriate value before running PACKDISK. |
If you set FSP to a value other than 0, tables are forced to occupy more cylinders than necessary. The extra space is not reclaimed until either you insert rows into the table, use the Ferret utility to initiate PACKDISK on a table, or until MiniCylPack is performed due to a lack of free cylinders.
When the system default FSP is greater than 0, use the information in the following table to minimize problems.
| IF you… | THEN… |
|---|---|
| use read-only tables | set FREESPACE on the CREATE TABLE statement to 0 before loading the table. If the table is loaded, use the ALTER TABLE statement to change FREESPACE to 0 before running PACKDISK. |
| add data via BTEQ or a CLI program | change nothing. The system adds rows at maximum density. |
Adjusting FREESPACE for a Table
You can specify the default value for free space left on a cylinder during certain operations using the FREESPACE option in the CREATE TABLE and ALTER TABLE statements.
You can select a different value for tables that are constantly modified versus tables that are only read after they are loaded. To specify the global free space value, use FreeSpacePercent (see “FreeSpacePercent” in Teradata Vantage™ - Database Utilities, B035-1102).