If a CREATE TABLE … AS statement does not specify 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.
- The system copies all the available statistics on the source table for single columns to the target table.
- The system copies all the available composite statistics on the source table columns to the target table except for any multicolumn statistics that include the system-derived PARTITION column.
- If no index definitions are specified for the target table, the system copies all available statistics on the source table indexes to the target table. The term zeroed statistics refers to the condition in which the synopsis data structures, or histograms (see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142), for the statistics on a column set or index have been constructed, but no statistics have been collected on the column set or index. A common example of this state is when you create a new table and then collect statistics on it when it does not yet contain any data rows.
- The following rules apply to copying PARTITION statistics from the source table to the target table.
IF you … THEN the system … define any explicit indexes for the target table does not copy any statistics that include the system-derived PARTITION column from the source table to the target table. do not define any explicit indexes for the target table copies any available single-column statistics on the system-derived PARTITION column or multicolumn statistics that include the system-derived PARTITION column from the source table to corresponding column set in the target table.