17.10 - Example: Statistics Not Copied - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Vantage 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, Vantage 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, Vantage 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;
Vantage 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;
Vantage 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.