Rules for Using AS … WITH NO DATA Clause | CREATE TABLE | Teradata Vantage - General Rules For CREATE TABLE AS … WITH NO DATA - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
The following list of rules applies only to an AS … WITH NO DATA clause. (see for the set of general rules that applies to the AS … WITH DATA AND STATISTICS clause)General Rules For CREATE TABLE AS … WITH DATA AND STATISTICS
  • If there are no columns or indexes in the target table for which statistics are eligible to be copied, the system returns a warming message to the requestor.
  • If you specify an explicit index definition for the target table, then the system does not copy PARTITION statistics from the source table to the target table.

    This is true for both single-column PARTITION statistics and for composite statistics on a column set that includes the system-derived PARTITION column.

  • If no statistics have been collected on the specified source table column or index sets, the system ignores the AND STATISTICS option and returns a warning message to the requestor.
  • If only a subset of the statistics from the source table are eligible to be copied to the columns and indexes of the target table, the system returns a warning message to the requestor.
  • If the number of multicolumn statistics you specify to be copied to the target table exceeds the maximum number of multicolumn statistics allowed (the maximum number of multicolumn statistics that can be collected and maintained for a table is 32), then the system copies multicolumn statistics only up to the limit, returns an error message to the requestor, and does not copy the remainder of the multicolumn statistics to the target table.
  • If you specify WITH NO DATA for a volatile source table, the system returns an error.
  • If all columns in a MULTISET source table are nonunique, and if the target table is a SET table, then the system copies zeroed statistics to the target table.
  • The database copies zeroed statistics from all eligible source table columns and indexes if you specify the NO DATA options for permanent data tables, and global temporary tables.

    The system also copies the current timestamp value for those source table statistics to the appropriate statistical histograms for the target table.

  • The database copies zeroed statistics when you specify a NOT CASESPECIFIC or UPPERCASE attribute for any column in the target table definition whether it matches the corresponding source table column attribute specification or not.
  • The database copies zeroed statistics for an index if you specify an index definition for the target table and it matches an existing index or multicolumn definition for the source table.
  • The database copies eligible composite index statistics from the source table as zeroed multicolumn statistics to the target table if the index on which they were collected is not defined for the target table.
  • The database copies eligible composite index statistics from the target table to the corresponding index on the target table when such an index is defined.
  • The database copies eligible multicolumn statistics on the source table as zeroed composite index statistics to the target table if the target table has an index defined on the corresponding columns.
  • If the target table has an index defined on the corresponding columns, the system copies eligible statistics on the source table to the target table as described by the following table.
    THIS type of source table statistics … IS copied to the target table as this type of statistics …
    single-column single-column.
    single-column index single-column index.
    single-column PARTITION single-column PARTITION.
    multicolumn multicolumn.
    composite index composite index.
    composite PARTITION composite PARTITION.
  • If the target table does not have an index defined on the corresponding columns, the system copies zeroed statistics on the source table to the target table as described by the following table.
    THIS type of source table statistics … IS copied to the target table as this type of zeroed statistics …
    single-column single-column.
    single-column index single-column index.
    single-column PARTITION single-column.
    multicolumn multicolumn.
    composite index composite index.
    composite PARTITION multicolumn.
  • The database copies single-column statistics on the source table as zeroed single-column statistics to the target table only if that column is eligible for its statistics to be copied.