17.10 - table_option - 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)

Specifies table options for the foreign table. Can be any table_option allowed in CREATE TABLE (see table_option), except as noted:

The following table options are not supported for foreign tables:
  • BLOCKCOMPRESSIONLEVEL
  • BLOCKCOMPRESSIONALGORITHM
  • BLOCKCOMPRESSION
  • MERGEBLOCKRATIO
  • CHECKSUM
  • DATABLOCKSIZE
  • FREESPACE
  • Journal table
The following table options are not supported for external data formatted as Parquet:
  • Row partitioning
  • Subrow partitioning
  • Multicolumn partitions
  • Autocompression
MAP
You can specify an existing contiguous or sparse map for the table.
Specifying a map is optional. If you do not specify a map, the default map is determined according to the following order of precedence:
  • If the immediate owner is not the creator:
    • Default map, if defined, for the profile of the immediate owner.
    • Default map, if defined, for the immediate owner.
    • System-default map.
  • Default map, if defined, for the profile of the creator.
  • Default map, if defined, for the creator.
  • System-default map.
See GRANT MAP in Teradata Vantage™ - SQL Data Control Language, B035-1149. Also, see the CREATE PROFILE DEFAULT MAP option and the CREATE USER DEFAULT MAP option.
map_name
Name of an existing contiguous or sparse map.
You cannot specify TD_DataDictonaryMap or TD_GlobalMap.
COLOCATE USING colocation_name
Optionally, you can specify a colocation name so that the tables reside on the same AMPs to avoid a redistribution of the rows when the tables are joined on a primary index or primary AMP index. For example, you can colocate two tables, then join the tables on the primary index or primary AMP index columns.
You can only specify this option for a sparse map. For a contiguous map, the colocation_name is not needed for colocation and is set to NULL.
If you do not specify a colocation name, the name defaults to database_table, where database is the name of the database followed by an underscore (_) and table is the name of the table. If database exceeds 63 characters, database is truncated to 63 characters. If table exceeds 64 characters, table is truncated to 64 characters.
For a CREATE TABLE AS source_table statement, where the map for the created table defaults to the sparse map of the source table, colocation_name defaults to the colocation name of the source table.