Teradata Database 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.
Example 1: Copying Single-Column Statistics
In this example, Teradata Database 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.|
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);
|Teradata Database 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.|
|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).