- The system does not copy statistics if you specify multiple table definitions in a subquery because multiple table specifications are joins, which change the data for the target table. This violates the rule that source and target table cardinalities must be identical. It is possible to resolve to a single source table when join elimination is done for multiple tables specified in a query. The system also does not copy statistics in this situation, as the rule specifies.
- The system does not copy statistics if none of the columns specified in the subquery of the CREATE TABLE … AS statement has a uniqueness constraint and the target is defined to be a SET table because this violates the rule that source and target table cardinalities must be identical.
However, if any column in the subquery is defined with a uniqueness constraint, then the system copies the statistics provided that all other eligibility requirements are met.
This is a specific restatement of the general rule stated in bullet number 7 under the topic General Rules For CREATE TABLE AS … WITH DATA AND STATISTICS.
- The system does not copy statistics for the system-derived column PARTITION to the target table when the source table is specified in a subquery. This is true both for single-column PARTITION statistics and for multicolumn statistics where the column set includes the system-derived PARTITION column.
- The system does not copy statistics to target table columns when the source relation in a subquery is a complex view or complex derived table.
- The system does not copy statistics for any column or index defined for the target table if only a proper subset of the data is copied from the base table because this violates the rule that source and target table cardinalities must be identical. For example, the system copies only partial data when you specify any of the following.
- Join conditions
- A DISTINCT operator
- A TOP n operator
- Aggregate functions
- OLAP functions
- A WHERE clause
- A GROUP BY clause
- A HAVING clause
- A QUALIFY clause
- Teradata Database does not copy statistics for any column or index defined for the target table if the source table data is modified in the target table definition.
Similarly, Teradata Database does not copy statistics for a composite index if the source table index data is changed when it is copied to the target table because this violates the rule about modifying target table data. An example of this would be the case where any of the index columns is specified with expressions in the subquery.
For example, data on a single column or index is modified if you specify a CASE expression or arithmetic expression in its target table definition.
- The system does not copy multicolumn statistics and composite index statistics if the left-to-right order of the columns specified in the select list are different from that of the left-to-right order of the source table columns. This is because the index or multicolumn values in the statistics are recorded in the field-id order of the columns in the set, so the values do not apply in the target table if the essential ordering of the underlying columns is not preserved.
- The system does not copy multicolumn statistics unless all of the component columns of the composite index or multicolumn set are copied to the target table.
- The system does not copy single column and single-column index statistics from a source table unless that column is also copied to the target table.
If a CREATE TABLE … AS statement specifies a subquery to define the source table column and index sets to be copied, the following rules apply in addition to those stated in General Rules For CREATE TABLE AS … WITH DATA AND STATISTICS.