17.00 - 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.00
Published
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)
Last Update
2021-01-23

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.