Example: Copying Zeroed Multicolumn and Composite Index Statistics - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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