16.20 - Example: Statistics Not Copied - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

Teradata Database does not copy statistics under the following scenarios.

Consider this table definition.

     CREATE SET TABLE t1, NO FALLBACK,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL,
       CHECKSUM = DEFAULT (
         x INTEGER,
         y CHAR(10) CHARACTER SET LATIN CASESPECIFIC,
         z INTEGER)
     UNIQUE PRIMARY INDEX (x);

Assume that single-column statistics have been collected on columns x, y, and z.

Example1

In this example, Teradata Database does not copy statistics because only partial data is copied. This occurs because neither y nor z is uniquely constrained, so the system eliminates all duplicate rows, which might cause a difference in cardinality between the 2 tables.

     CREATE SET TABLE t9(x, y) AS (SELECT y, z
                                   FROM t1) 
     WITH DATA AND STATISTICS 
     PRIMARY INDEX(x, y);

Example2

In this example, Teradata Database copies partial statistics. The single-column statistics from source table columns x and z are copied as single-column statistics for target table columns a and c, respectively.

Single-column statistics on y in the source table are not eligible to be copied to target table column b in the target table because the NOT CASESPECIFIC column attribute of b is not the same as the column default attribute of source table column y.

     CREATE TABLE t9(a, b NOT CASESPECIFIC, c) AS (SELECT x, y, z
                                                   FROM t1) 
     WITH DATA AND STATISTICS;
Teradata Database copies these statistics … From this source table column set … To this target table column set … Because …
single-table x a the columns x and z in source table t1 and columns a and c in target table t9 are identical, so the system copies their single-column statistics.
z c
y none the definition of column b in the target table is changed from the definition of its analog column y in the source table because it adds a NOT CASESPECIFIC attribute.

Example3

In this example, single-column statistics on source table column y are not eligible to be copied for target table column x because data for this column is modified in the target table with the UPPERCASE attribute.

     CREATE TABLE t9(x UPPERCASE, y) AS (SELECT y, z
                                         FROM t1) 
     WITH DATA AND STATISTICS;
Teradata Database copies these statistics … From this source table column set … To this target table column set … Because …
single-table z y column z in source table t1 and column y in target table t9 are identical, so the system copies their single-column statistics.
y none the definition of column x in the target table is changed from the definition of its analog column y in the source table because it adds an UPPERCASE attribute.