FREESPACE PERCENT | CREATE TABLE (Table Options Clause) | Vantage - FREESPACE PERCENT - Advanced SQL Engine

FREESPACE PERCENT | CREATE TABLE (Table Options Clause) | Vantage - FREESPACE PERCENT - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product

Advanced SQL Engine

Teradata Database

Release Number

17.10

Published

July 2021

Language

English (United States)

Last Update

2021-07-27

dita:mapPath

imq1591724555718.ditamap

dita:ditavalPath

imq1591724555718.ditaval

dita:id

B035-1184

lifecycle

Product Category

Teradata Vantage™

FREESPACE = n PERCENT specifies the value for the percent freespace attribute, where n is an integer constant. n percent free space remains on each cylinder during bulk data load operations on the table. PERCENT is an unnecessary keyword and does not affect the semantics of the specification.

Not all database operations use the freespace percentage you specify in a CREATE TABLE statement. The following table lists which operations use the specified freespace percentage and which do not.

Operations That Use FREESPACE	Operations That Do Not Use FREESPACE
FastLoad data loads MultiLoad data loads into unpopulated tables Table Rebuild System reconfiguration Ferret PACKDISK MiniCylPack If few cylinders are available, and storage space is limiting, MiniCylPack might not be able to honor the specified FREESPACE value. ALTER TABLE statement that adds fallback protection to a table CREATE INDEX statement that defines or redefines a secondary index on a populated table Creation of a fallback table during an INSERT … SELECT operation into an empty table that is defined with fallback. Creation of a secondary index during an INSERT … SELECT operation into an empty table that is defined with a secondary index.	SQL INSERT operations Teradata Parallel Data Pump INSERT operations MultiLoad data loads into populated tables.

Operations That Use FREESPACE

Operations That Do Not Use FREESPACE

FastLoad data loads
MultiLoad data loads into unpopulated tables
Table Rebuild
System reconfiguration
Ferret PACKDISK
MiniCylPack
If few cylinders are available, and storage space is limiting, MiniCylPack might not be able to honor the specified FREESPACE value.
ALTER TABLE statement that adds fallback protection to a table
CREATE INDEX statement that defines or redefines a secondary index on a populated table
Creation of a fallback table during an INSERT … SELECT operation into an empty table that is defined with fallback.
Creation of a secondary index during an INSERT … SELECT operation into an empty table that is defined with a secondary index.

SQL INSERT operations
Teradata Parallel Data Pump INSERT operations
MultiLoad data loads into populated tables.

The FREESPACE value returned in response to a SHOW TABLE statement is either the value specified in the CREATE TABLE statement for the table or the value specified by the most recent ALTER TABLE statement.

Do not use the Ferret utility PACKDISK command to change this value once you have created a table or have modified its FREESPACE PERCENT with an ALTER TABLE statement.

Instead, submit an ALTER TABLE statement to change the free space percent value for the table and then immediately afterward submit a PACKDISK command that specifies the same free space percent value you set with that ALTER TABLE statement. See ALTER TABLE (Basic Table Parameters). For information about running PACKDISK, see the Ferret utility in Teradata Vantage™ - Database Utilities, B035-1102.

If you manually submit a PACKDISK command and specify a free space percentage on the command line, PACKDISK packs or unpacks the cylinders for the table to match the specified free space percentage. However, if the free space percentage you specify differs from the value specified for the table when it was created or last altered, Vantage starts a new AutoCylPack task immediately after PACKDISK completes, and this AutoCylPack operation nullifies the free space percentage you had just specified with PACKDISK to realign it with the free space percentage specified when you created or last altered the table.