General Rules For CREATE TABLE AS … WITH DATA AND STATISTICS - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
The following list of rules applies only to an AS … WITH DATA AND STATISTICS clause. These rules do not affect an AS … WITH NO DATA clause. See General Rules For CREATE TABLE AS … WITH NO DATA.
  • 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, does not copy the remainder of the multicolumn statistics to the target table, and reports a warning message to the requestor.
  • If all columns in a MULTISET source table are nonunique, and if the target table is a SET table, then the system does not copy statistics to the target table. This is because of the possible violation of the rule of equal cardinalities in the source and target tables: if there are duplicate rows in the source table, the system eliminates them before copying to the target table, resulting in unequal cardinalities between the 2 tables.
  • If the source table has at least 1 nonunique column and the target table is a SET table, then the system copies the statistics from the source table to the target table if all other rules are also obeyed.
  • If you specify the NOT CASESPECIFIC attribute for any column in the target table definition, and it does not match the corresponding source table column attribute specification, the system does not copy the statistics for that column or index set because of the possible violation of the rule about not modifying the data in the target table.
  • If all the columns in the source table are nonunique and you specify the NOT CASESPECIFIC attribute for any column in the target table definition that does not match the corresponding source table column attribute definition, then the system does not copy the statistics for that column or index because of the possible violation of the rule of equal cardinalities in the source and target tables.
  • If you specify the UPPERCASE attribute for a column in the target table definition that does not match with the corresponding source table column attribute definition in the source table, then the system does not copy statistics for any column or index because of the possible violation of the rule about not modifying the data in the target table.
  • Teradata Database copies single-column index statistics from the source table to the target table as single-column column statistics in the target table if no target table index is defined on the corresponding column set in the target table.
  • Teradata Database copies single-column column statistics from the source table to the target table as single-column index statistics in the target table if an index is defined on that column in the target table.
  • Teradata Database copies composite index statistics from the source table to the target table as multicolumn statistics if the source table index definition is not defined for the target table.
  • Teradata Database copies multicolumn statistics from the source table to the target table as composite index statistics if the target has an index defined on that column set.