Example: Copying Statistics for All Columns - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Vantage copies statistics for all of the following scenarios because the underlying index and column data is not modified when it is copied as these examples demonstrate.

Example 1

In this example, Vantage copies the statistics for columns for x1 and y1 from source table test1 to columns a and b in target table t6. The system copies the statistics on the composite index on the source table column set (x1, y1) as multicolumn statistics for the column set (a, b) in the target table t6 because no index is explicitly defined on that column set in t6.

    CREATE TABLE t6 (a, b, c) AS (SELECT x1, y1, z1 (AS colC)
                                  FROM test1) 
    WITH DATA AND STATISTICS;

The shaded cells in the table indicate where the system copies statistics when they do not correspond directly to a column set in the source table.

Statistics Copied Source Table Column Set Target Table Column Set Description
single column x1 a Columns x1 and y1 in source table test1 map directly to columns a and b in target table t6.
y1 b
single-column NUSI z1 c Column z1 in source table test1 maps directly to column c in target table t6.
multicolumn none (a, b) Columns x1 and y1 in source table test1 map directly, and in the same order, to columns a and b in target table t6, so the multicolumn statistics on (x1,y1) in test1 can be used as multicolumn statistics on (a,b) in t6.
multicolumn NUPI (x1, y1) none

Example 2

In this example, Vantage copies the following column and index statistics.

    CREATE TABLE t6 (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 y None. Column y does not have an analog in table t6.
x a Column a in table t6 is a direct analog of column x in table test.
a c The index statistics collected on NUPI column a in table test can be used as column statistics for column c in table t6.
single-column NUPI a None.

Statistics for index column a in test are copied to non-index column c in t6.

A NUPI is not defined for target table t6.

However, the statistics collected for the NUPI on column a in source table test can be used as column statistics for column c in table t6.

multicolumn (a, b) None. The analogs of columns a and b from source table test are defined in a different order in target table t6. Therefore, their multicolumn statistics cannot be used for the new column set.
multicolumn NUSI (x,y) None. Column y does not have an analog in table t6.

Example 3

In this example, Vantage copies the following statistics.

    CREATE TABLE t6 (a, b, c) AS (SELECT k1 AS colA, y1 AS colB, 
                                         z1 AS colC)
                                  FROM test1) 
    WITH DATA AND STATISTICS;
Statistics Copied Source Table Column Set Target Table Column Set Description
single column k1 a Columns a and b in target table t6 are direct analogs of columns k1 and y1 in source table test1.

The single-column statistics for table test1 columns k1, y1, and z1 are copied to t6 for the single columns a, b, and c, respectively.

y1 b
single-column NUSI z1 c A NUSI is not defined for target table t6.

However, the statistics collected for the index on column z1 in source table test1 map directly to column c in target table test1.

multicolumn NUPI (x1,y1) none Column x1 does not have an analog in source table test1 in target table t6.

Example 4

In this example, Vantage copies single column statistics on x1 and y1 for a and b, and it copies index statistics for the column set (a, b).

    CREATE TABLE t6 (a, b) AS (SELECT x1 AS colB, y1 (AS colC)
                               FROM test1) 
    WITH DATA AND STATISTICS 
    PRIMARY INDEX (a, b);
Statistics Copied Source Table Column Set Target Table Column Set Description
single column x1 a Columns a and b in target table t6 are analogs of columns x1 and y1 in source table test1.
y1 b
single-column NUSI z1 None. Source table test1 column z1 does not have an analog in target table t6.
multicolumn NUPI (x1, y1) (a, b) Columns a and b in target table t6 are analogs of columns x1 and y1 in source table test1 and are also defined in the same order in t6 as they were in test1.

Example 5

In this example, the system copies statistics from source table test to target table t6 only for column x.

    CREATE TABLE t6 AS (SELECT * 
                        FROM (SELECT x
                              FROM test) AS d1) 
    WITH DATA AND STATISTICS;
Statistics Copied Source Table Column Set Target Table Column Set Description
single column x d1 Column x from source table test is the only column that is qualified by the nested subqueries.
single-column NUPI a None. Source table column a does not have an analog in target table t6.
multicolumn
  • (a,b)
  • (x,y,z)
None. The only column in target table t6 is an analog of column x in source table test.
multicolumn NUSI (x, y) None. The only column in target table t6 is an analog of column x in source table test.

Example 6

In this example, the system copies all the available single column statistics, multicolumn statistics, and index statistics from table test1 to table t6.

    CREATE TABLE t6 AS test1
    WITH DATA AND STATISTICS;
Statistics Copied Source Table Column Set Target Table Column Set Description
single column x1 x1 All columns from source table test1 and their statistics are copied to the same columns (with the same names) in target table t6.
single column y1 y1 All columns from source table test1 and their statistics are copied to the same columns (with the same names) in target table t6.
single column k1 k1 All columns from source table test1 and their statistics are copied to the same columns (with the same names) in target table t6.
single-column NUSI z1 z1 All columns from source table test1 and their statistics are copied to the same columns (with the same names) in target table t6.
multicolumn NUPI (x1, y1) (x1, y1) All columns from source table test1 and their statistics are copied to the same columns (with the same names) in target table t6.

Example 7

In this example, the system copies all the available single column statistics, multicolumn statistics, and index statistics from table test to table t6.

The statistics that had been collected for the column set (x, y, z) in test are now used for the new multicolumn NUPI defined on the same column set in table t6.

The multicolumn statistics defined on (x, y, z) for test are copied to t6 for the same column set, which is defined as the multicolumn NUPI for that table.

    CREATE TABLE t6 AS test
    WITH DATA AND STATISTICS 
    PRIMARY INDEX (x, y, z);

The shaded cells in the table indicate where the system copies statistics when they do not correspond directly to a column set in the source table.

Statistics Copied Source Table Column Set Target Table Column Set Description
single column x x Columns x and y from source table test are analogs, having the same column names in target table t6.
single column y y Columns x and y from source table test are analogs, having the same column names in target table t6.
single column none a The system copies NUPI column a from source table test as non-index column a to target table t6.

Its single-column index statistics from source table test are copied as single-column non-index statistics to target table t6.

single-column NUPI a None. The system copies NUPI column a from source table test as non-index column a to target table t6.

Its single-column index statistics from source table test are copied as single-column non-index statistics to target table t6.

multicolumn (a, b) (a, b) the source and target table columns are analogous and defined in the same order in both tables.
multicolumn (a, b) (x,y) The source and target table columns are analogous and defined in the same order in both tables.

The system copies the multicolumn index statistics from source table test as multicolumn statistics to target table t6.

multicolumn NUSI (x, y)   The source and target table columns are analogous and defined in the same order in both tables.

The system copies the multicolumn index statistics from source table test as multicolumn statistics to target table t6.

multicolumn (x,y,z)   The source and target table columns are analogous and defined in the same order in both tables.

The system copies the multicolumn statistics from source table test as multicolumn index statistics to target table t6.

multicolumn NUPI   (x, y, z) The source and target table columns are analogous and defined in the same order in both tables.

The system copies the multicolumn statistics from source table test as multicolumn index statistics to target table t6.

Example 8

In this example, the system copies all the available single-column statistics, multicolumn statistics, and index statistics for table test.

    CREATE TABLE t6 AS test
    WITH DATA AND STATISTICS 
    PRIMARY INDEX (x, y, z) 
    INDEX (a, b);

The shaded cells in the table indicate where the system copies statistics when they do not correspond directly to a column set in the source table.

Statistics Copied Source Table Column Set Target Table Column Set Description
single column x x Columns x and y from source table test are analogs and have the same column names in target table t6.
single column y y columns x and y from source table test are analogs and have the same column names in target table t6.
single column   a the system copies NUPI column a from source table test as non-index column a to target table t6.

Its single-column index statistics from source table test are copied as single-column non-index statistics to target table t6.

single-column NUPI a   the system copies NUPI column a from source table test as non-index column a to target table t6.

Its single-column index statistics from source table test are copied as single-column non-index statistics to target table t6.

multicolumn (x, y, z)   the multicolumn statistics on column set (x, y, z) in source table test are copied as multicolumn index statistics for the multicolumn NUPI on the same column set in target table t6.
multicolumn (a,b)   The multicolumn statistics on column set (a,b) in source table test are copied as multicolumn index statistics for the multicolumn NUSI on the same column set in target table t6.
multicolumn   (x,y) The multicolumn NUSI statistics on column set (x,y) in source table test are copied as multicolumn non-index statistics on the same column set in target table t6.
multicolumn NUPI   (x, y, z) the multicolumn statistics on column set (x, y, z) in source table test are copied as multicolumn index statistics for the multicolumn NUPI on the same column set in target table t6.
multicolumn NUSI (x, y) (a, b) The multicolumn NUSI statistics on column set (x,y) in source table test are copied as multicolumn non-index statistics on the same column set in target table t6.

The multicolumn statistics on column set (a,b) in source table test are copied as multicolumn index statistics for the multicolumn NUSI on the same column set in target table t6.

The multicolumn statistics (x, y, z) that had been collected in test are now used for the new multicolumn NUPI defined on the same column set in t6, while the multicolumn statistics on (a, b) that had been collected in test are now used for the new multicolumn NUSI defined on the same column set in t6.

Example 9

In this example, the system copies all the single-column statistics, multicolumn statistics, and the index statistics for the multicolumn NUSI (x, y).

    CREATE TABLE t6 AS (SELECT *
                        FROM test) 
    WITH DATA AND STATISTICS 
    PRIMARY INDEX(x, y);

The shaded cells in the table indicate where the system copies statistics when they do not correspond directly to a column set in the source table.

Statistics Copied Source Table Column Set Target Table Column Set Description
single column x x Column x from source table test is an analog and has the same column name in target table t6.
single column y y Column y from source table test is an analog and has the same column name in target table t6.
single column   a The system copies its single-column index statistics from source table test as single-column non-index statistics to target table t6.
single-column NUPI a   The system copies NUPI column a from source table test as non-index column a to target table t6.
multicolumn (x, y, z) (x, y, z) The system copies the multicolumn statistics on both column sets directly from source table test to target table t6 because the columns are defined identically for both.
multicolumn (a,b) (a,b) The system copies the multicolumn statistics on both column sets directly from source table test to target table t6 because the columns are defined identically for both.
multicolumn NUPI   (x, y) The system copies multicolumn index statistics on the composite NUSI column set (x,y) of source table test as multicolumn index statistics on the composite NUPI on the same columns in target table t6.
multicolumn NUSI (x, y)   The system copies multicolumn index statistics on the composite NUSI column set (x,y) of source table test as multicolumn index statistics on the composite NUPI on the same columns in target table t6.

The statistics that had been collected for the single columns x and y and for the column sets (x, y, z) and (a, b) in test are copied straight across into the same columns sets for t6, while the statistics that had been collected for the multicolumn NUSI on 
(x, y) in test are now used for the new composite NUPI defined on the same column set in t6.

The multicolumn NUSI that was defined for test is not defined for the new table t6, but the system does copy its statistics for use with the new multicolumn NUPI defined on the same column set in t6.

Example 10

In this example, the system copies all the single-column statistics, multicolumn statistics, and index statistics from test to t6.

    CREATE TABLE t6 AS test
    WITH DATA AND STATISTICS;
Statistics Copied Source Table Column Set Target Table Column Set Description
single column x x The source and target tables are identical to one another.
y y
single-column NUPI a a
multicolumn (x, y, z) (x, y, z)
(a,b) (a,b)
multicolumn NUSI (x, y) (x, y)