CREATE TABLE and CREATE TABLE AS Syntax Elements (Object File System Table Form) - Teradata Vantage

MULTISET

Table can have duplicate rows unless there are uniqueness constraints on any column or set of columns in the table definition.

database_name

user_name

[Optional] Name of database or user to contain table, if different from current default database.

Default: default database for current session

table_name

Name for the new table.

STORAGE = TD_OFSSTORAGE

Specifies that the table is in native object storage (an Object File System table).

If you specify a nonexistent storage object, Vantage uses the default storage object and warns you, unless an applicable profile, user, or database specifies OVERRIDE NOT ON ERROR.

Specifying multiple storage objects is an error.

map_name

[Optional] Existing contiguous or sparse map for the table (other than TD_DataDictonaryMap or TD_GlobalMap).

If not the default map, you must have been granted access to map_name.

Default: 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.

colocation_name

[Optional for sparse map, disallowed for contiguous map] Has no effect, because an Object File System table has no primary index or primary AMP index.

For tables with a primary index or primary AMP index, specifying the same colocation name for different tables puts those tables on the same AMPs, avoiding redistribution of rows when the tables are joined on a primary index or primary AMP index.

Default:

• CREATE TABLE: database_name_table_name

  If database exceeds 63 characters, database is truncated to 63 characters. If table exceeds 64 characters, table is truncated to 64 characters.

• CREATE TABLE AS: colocation name of source_table

NO ORDER BY

[Optional] Specifies that the data in the objects being inserted into the new Object File System table are not to be sorted into columns. This is the default.

ORDER BY order_column_name [,…]

[Optional] Specifies the column or columns on which to sort the data in the objects being inserted into the new Object File System table.

order_column_name cannot have a LOB data type.

Values for order_column_name are limited to 4 or fewer bytes.

Default: NO ORDER BY

PRIMARY INDEX

Specifies primary index definition. The primary index is used by the hashing algorithm to distribute data rows of table to AMPs during load to build data objects on hash bucket ranges. When PRIMARY INDEX clause is not specified, table is defaulted to NO PRIMARY INDEX.

index_column_name

Specifies a column in the column set that defines a partitioned primary index.

If you specify more than one column name, the index is created on the combined values of each column named.

You cannot define a primary index on a column defined with any of the following data types:

• BLOB
• CLOB
• Period
• XML
• Geospatial
• JSON
• DATASET

index_name

Specifies the optional name for the index.

For information about naming database objects, see SQL Fundamentals.

UNIQUE

Specifices that the primary index is to be unique.

Any primary index can be defined to be unique with the exception that PI whose definition does not include all the ORDER BY columns, if any.

NO PRIMARY INDEX

[Optional] Specifies that the table has no primary index. This is the default.

PARTITION BY COLUMN

[Optional] Specifies that the table is partitioned by column (an Object File System columnar table). This is the default.

NOT PARTITIONED

[Optional] Use if you want the Object File System table to be in row format.

AS_clause

Source of column definitions for the new Object File System table, either the name of an existing table or a subquery on an existing table.

WITH DATA copies both the column definitions and data from the source to the new table. WITH NO DATA copies only the column definitions.

WITH DATA AND STATISTICS sets up the appropriate statistical histograms in the dictionary for the new table and copies the statistics from the source into them.

WITH DATA AND NO STATISTICS sets up the appropriate statistical histograms in the dictionary for the new table but does not copy the statistics from the source into the histograms. The new table has zeroed statistics, and you must collect statistics for the table after the table is populated.

Copied PARTITION statistics may not correctly represent the data in the new table because of differences in internal partition number mapping between the source and new tables. This is true even if the tables have the same data and SHOW TABLE returns identical table definitions for them. Therefore, you must recollect the PARTITION statistics for the new table.

See CREATE TABLE with STORAGE Option Usage .