17.10 - USING FAST MODE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)
You can use this table_option with only two kinds of statements:
  • ALTER TABLE statement with no alter_option

    USING FAST MODE can only change the table format.

  • ALTER TABLE statement whose only alter_option is ADD COLUMN

    USING FAST MODE can change the table format and add columns.

The table format is either Fast Column Add (FCA) or non-FCA.

For a table in FCA format:
  • Every ADD COLUMN statement on the table includes an implicit USE FAST MODE ON clause.
  • If you update a column, columns with versions greater than the version of the updated column (v) do not materialize in the row if the row version is less than v.
  • If you drop one or more columns, the table converts to non-FCA format. Missing values in remaining columns materialize in the rows, which may increase table size.
FCA format advantages:
  • Dramatically reduces exclusive lock times that ADD COLUMN needs.
  • Adds columns without increasing table space, because added columns do not materialize immediately in existing rows.
FCA format does not support the following:
  • ADD COLUMN statements that increase row size from less than 64 KB to more than 1 MB
  • Column-partitioned tables
  • Dictionary tables
  • VOLATILE tables
  • GLOBAL TEMPORARY tables
  • QUEUE tables
  • ERROR tables
  • Unity TD_Rowsize expression

    Row size on managed servers can differ, and Unity does not support different values for the same field.

ON
Default when DBSControl flag FastAlterEnable has its default value, true.
If the table format is non-FCA, it becomes FCA.
If the ALTER TABLE statement includes one or more ADD COLUMN options, the statement adds the specified column or columns to the table without altering the existing rows and adds the default values of the new columns to the table header.
OFF
If the table format is FCA, it becomes non-FCA. FCA structures in the table header disappear, and missing values in remaining columns materialize in the rows, which may increase table size.
If the ALTER TABLE statement includes one or more ADD COLUMN options, the statement adds the specified column or columns to the table.