15.00 - Specific Rules For CREATE TABLE AS WITH DATA AND STATISTICS No Subquery - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Specific Rules For CREATE TABLE AS … WITH DATA AND STATISTICS That Does Not Use a Subquery To Define The Source Table Column And Index Set

If a CREATE TABLE … AS request 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” on page 663.

  • 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 composite column 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 SQL Request and Transaction Processing), 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 composite column statistics that include the system‑derived PARTITION column from the source table to corresponding column set in the target table.