15.10 - Example: Copying Zeroed Multicolumn and Composite Index Statistics - 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 copies multicolumn zeroed statistics and zeroed composite index statistics even if the relative order of the index columns specified in the select list of the subquery is not the same as their order in the source table.

Example 1: Copying Single-Column Statistics

In this example, Teradata Database copies the single-column statistics on columns x and a in test as zeroed single-column statistics for columns a and c in t2. The multicolumn statistics on (a,b) are not copied as zeroed multicolumn statistics on columns (b,c) in t2 because the order of columns b and c in t2 is different than the order of columns b and a in test.

     CREATE TABLE t2 (a, b, c) AS (SELECT x AS colA, b, a (AS colC)
                                   FROM test) 
     WITH NO DATA AND STATISTICS;
Teradata Database copies these zeroed statistics … From this source table column set … To this target table column set … Because …
single column x a column a in target table t2 is the exact analog of column x in source table test.
y none there is no analog of source table column y in target table t2.
single-column NUPI a c column c in target table t2 is the exact analog of column a in source table test.
multicolumn (a,b) none the order of columns b and c in the definition of target table t2 is different than the order of columns b and a in the definition of source table test.
(x,y,z)   there is no multicolumn index set defined for source table t2 on an analogous column set to (x,y,z) and (x,y) in source table test.
multicolumn NUSI (x,y) none there is no multicolumn index set defined for source table t2 on an analogous column set to (x,y,z) and (x,y) in source table test.

Example 2: Copying Single-Column Statistics

In this example, Teradata Database copies the single-column statistics from test1 as zeroed single-column statistics on columns a , b , and c in t2. The index statistics on (x1,y1) are not copied as zeroed index statistics on columns (a,b) in t2 because the order of columns a and b in t2 is different than the order of columns x 1 and y1 in test1.

     CREATE TABLE t2 (a, b, c) AS (SELECT k1 AS colA, y1 AS colB, 
                                          z1 AS colC)
                                   FROM test1) 
     WITH NO DATA AND STATISTICS 
     INDEX(a, b);
Teradata Database copies these zeroed statistics … From this source table column set … To this target table column set … Because …
single column k1 a the new columns in target table t2 are exact analogs of the existing columns in source table test1.
y1 b
z1 c
  • x1
  • a1
  • b1
none there are no analogs of these source table columns defined in target table t2.
single-column NUSI z1 none there is no single-column NUSI defined on the analog to source table column z1, column c, in the source table; instead, there is a multicolumn NUSI defined on the column set (a,b).

The NUSI on (a,b) in target table t2 is defined without statistics being copied because no statistics were collected on their analog column set, (k1,y1), in source table test1.

multicolumn NUPI (x1,y1) none the order of the index columns specified in the subquery select list is different than the column order of the test1 index.