table_options - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Specifies table options for the foreign table.

For external data formatted as Parquet, you cannot specify the following table-level options:
  • 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.