CREATE TABLE with STORAGE Option Usage - Teradata Vantage

General

• Table-level options can appear in any order.
• If more than one storage option is specified in the table options, an error is returned.
• If a storage option is specified, for it to be valid,
  • The storage name must identify an existing storage object.
  • The user must have the specified storage except when the specified storage is the same as the default storage chosen by rules 2 to 5 (shown below) for when a storage option is not specified.
  If the storage name is invalid, a default storage is used unless OVERRIDE NOT ON ERROR is specified for an applicable profile, user, or database. If OVERRIDE NOT ON ERROR is specified, the following occur:

  • An error is returned corresponding to the reason the storage option is invalid.
  • A warning is returned when the invalid storage is overridden by a default storage. The warning indicates the name of the default storage used.
• If a storage option is not specified, a default storage used is chosen based on the following precedence rules:
  1. For a CREATE TABLE AS source_table statement, the storage of the source table is chosen. This storage must be the same as the default storage from the following precedence rules; otherwise, an error occurs unless there is an applicable OVERRIDE ON ERROR (see example 4).
  2. If the immediate owner for the table is not the creator:
     1. If the immediate owner has a profile with a default storage, the profile’s default storage is used.
     2. If the immediate owner has a default storage, that default storage is used
     3. Otherwise, the system default storage is used.
  3. If the creator has a profile with a default storage, that default storage is used.
  4. If the creator has a default storage, that default storage is used.
  5. Otherwise, the system default storage is used.
  CREATE TABLE … AS query follows the preceding precedence rules 2 through 5, if a storage option isn't specified, regardless of whether the query references 0, 1, 2, or more tables.
• Unless there is an OVERRIDE NOT ON ERROR option for an applicable default storage for a profile, user, or database, elements of the CREATE TABLE statement that are not valid (see example 4) for that particular storage are ignored.

  For example, specifying PRIMARY INDEX on a table in the Object File System is not valid, so it is ignored (that is, an error is not returned) and the table is created with no primary index. A warning is returned if an element of the CREATE TABLE statement is ignored.

• An Object File System table is MULTISET by default. You can also specify MULTISET explicitly.

  Trying to create an Object File System table as a SET table is an error.

• Trying to create an Object File System table of an unsupported type (such as temporal) is a syntax error.
• An Object File System table has NO PRIMARY INDEX by default. NO PRIMARY INDEX may be specified explicitly.

  Trying to create a primary index, primary AMP index, or secondary indexes, is an error.

• An Object File System table may optionally be created specifying PARTITION BY COLUMN.

  Trying to specify other forms of partitioning including other columnar options for the PARTITION BY COLUMN clause is an error.

  For an Object File System table, PARTITION BY COLUMN indicates Parquet columnar for the table’s data objects and not Teradata Columnar.

• For an Object File System columnar table, columns cannot have column level compression (MVC or ALC).

  Trying to specify one is an error.

• Permanent space in primary cluster block storage in the specified or default map is used for storing table headers associated with an Object File System table. A sparse map (such as a single-AMP sparse map) can be used to reduce the space used in block storage.
• A user cannot create an Object File System table as a global temporary table, a volatile table, or an error table; otherwise, an error is returned if specified.

ORDER BY Clause

ORDER BY is an important clause to improve performance. By grouping similar values into objects, fewer objects need to be read at query time.

For example, on an initial load of data into a sales table, ordering on a sales date means that queries that want last month's sales need not read all of the objects.

CREATE TABLE AS Statement

• The source and target tables can have different storage types.
• The storage option can be used to explicitly specify the storage of the created table.
• If the storage option is not specified and the source is a Block File System table (not in a SELECT), the table is created in the Block File System system, TD_NDSSTORAGE.
• If the storage option is not specified and the source is an Object File System table (not in a SELECT), an Object File System table is created with the same storage as the source (that is, TD_OFSSTORAGE).
• If the storage option is not specified and the source is a SELECT, the storage of the created table depends on the default storage applicable as determined earlier.
• A file system table can be created from an Object File System table by specifying TD_NDSSTORAGE as the name in the storage option.
  • If the Object File System table contains syntax elements (for example, the ORDER BY clause) that are not supported in a file system, the target is still created ignoring the unsupported syntax with a warning.
  • If the table is created using table copy option, the target file system table is MULTISET, FALLBACK and NO PRIMARY INDEX.
  • If the table is created using select option, the target file system table is SET (or MULTISET in ANSI mode), FALLBACK, and PRIMARY INDEX on first column.
• An Object File System table can be created from a file system table by specifying TD_OFSSTORAGE (if this storage has been provided during provisioning) as the name in the storage option. If the source file system table contains syntax elements that are not supported in the Object File System, the target table is still created with a warning.
  • If the source table contains properties such as SET, FALLBACK, PRIMARY INDEX, or PARTITION BY (with only row partitioning), the target Object File System table is created with properties as MULTISET, NO FALLBACK and NOPI but with a warning.
  • If source table contains a PARTITON BY property specifying column partitioning, the target Object File System table is created with the property PARTITION BY COLUMN, but without any other source column partitioning options or any row partitioning from the source’s PARTITION BY property. If any options of the source PARTITION BY properties are ignored, a warning is returned.
  • If the source contains an SI or a unique constraint, a warning is returned.
• If WITH DATA is specified, the data from the source is inserted into the created table. For an Object File System table, new objects are created for the inserted data. For Object File System tables, there is no sharing of objects between tables.

NOT PARTITIONED Keyword

If a row-based table is needed, NOT PARTITIONED must be explicitly stated. See the following example to create an Object File System row-based table.

CREATE MULTISET TABLE Orders,

STORAGE=TD_OFSSTORAGE
( StoreNo SMALLINT 
, OrderId INTEGER 
, OrderDate DATE FORMAT 'YYYY-MM-DD’ 
, OrderTotal INTEGER 
) 

ORDER BY (OrderDate, OrderTotal)  
NO PRIMARY INDEX
NOT PARTITIONED
;