17.00 - Example: Copying Statistics - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

Vantage copies multicolumn or index statistics even if the relative order of the columns specified in the select list of the subquery is not the same as is defined for the source table.

When you copy PARTITION statistics, the statistics copied to the target table might not correctly represent the data in the target table because of differences in internal partition number mapping between the source and target tables, even if the table definitions returned by a SHOW TABLE request are identical and the data is the same in both tables.

You cannot assume a target table created using a CREATE TABLE … AS … WITH DATA AND STATISTICS request will be identical to the source table from which its statistics are copied down to the level of internal partition numbers, even though the two tables might appear to be identical from comparing their definitions using the output of SHOW TABLE requests on the tables. In general, you should always recollect the PARTITION statistics for the target table when you copy them from a source table.

Example 1: Copying Single-Column Statistics

In this example, Vantage copies single-column statistics on columns x and a as single-column statistics for columns a and c, respectively. Multicolumn statistics on (a, b) are not copied as multicolumn statistics for (b, c) because the order of columns b and c in the target table is different from the order of columns b and a in the source table.

     CREATE TABLE t2 (a, b, c) AS (SELECT x AS colA, b, a (AS colC)
                                   FROM test) 
     WITH DATA AND STATISTICS;
Statistics Copied Source Table Column Set Target Table Column Set Description
single column x a Column x in source table test maps directly to column a in target table t2.
single column y none Source table test column y does not have an analog in target table t2.
single-column NUPI a c Column a in source table test maps directly to column c in target table t2.
multicolumn (x,y,z) none Source table test column y does not have an analog in target table t2.
multicolumn (a,b) none Order of columns (b,c) in target table t2 is different than the order of columns (a,b) in source table test.
multicolumn NUSI (x,y) none
  • Source table test column y does not have an analog in target table t2.
  • A NUSI is not defined for target table t2.

Example 2: Copying Single-Column Statistics in Relative Order

In this example, the system copies single-column statistics on k1, y1, and z1 as single-column statistics for a, b, and c, respectively. The system does not copy index statistics on (x1, y1) as index statistics for (a, b) because the order of a and b in the target table t2 is not the same as the order of x1 and y1 in the source table test1.

     CREATE TABLE t2 (a, b, c) AS (SELECT y1 AS colA, x1 AS colB, 
                                          z1 AS colC)
                                   FROM test1) 
     WITH DATA AND STATISTICS 
     INDEX(a, b);
Vantage copies these statistics … From this source table column set … To this target table column set … Because …
single column x1 b columns x1 and y1 in source table test1 map directly to target table t2 columns b and a, respectively.
y1 a
k1 none there is no analog of source table test1 column k1 in target table t2.
single-column NUSI z1 c source table test1 column z1 maps directly to target table t2 column c.
multicolumn NUSI none (a,b) the order of columns (a,b) in target table t2 is different than the order of columns (x1,y1) in source table test1.

Note that their ordering in t2, if they had retained the same column names, would be (y1, x1).

multicolumn NUPI (x1,y1) none