Specific Rules For AS … WITH DATA AND STATISTICS That Uses A Subquery To Define The
Source Table Column And Index Set
If a CREATE TABLE … AS request 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” on page 663.
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 request 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
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” on page 663.
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 composite column 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.
A DISTINCT operator
A TOP n operator
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 composite column 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 (either for a composite index or a
pseudoindex, see “Location of Stored Statistics” on page 178 for a definition of pseudoindexes) 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.