15.10 - Example: Statistics Not Copied - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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.