17.10 - Example: Copying Zeroed Statistics - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

The examples in this set copy zeroed statistics. The examples are based on the following source table definitions for test and test1.

    CREATE SET TABLE test, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT (
       x INTEGER,
       y INTEGER,
       z CHARACTER(30),
       a INTEGER,
       b DATE,
       e INTEGER)
    PRIMARY INDEX (a),
    INDEX(x, y);

Assume you collect the following statistics on test.

Statistics Test Table Column Set
single column
  • x
  • y
single-column NUPI a
multicolumn
  • (x, y, z)
  • (a, b)
multicolumn NUSI (x, y)
    CREATE SET TABLE test1, NO FALLBACK,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT (
       x1 INTEGER,
       y1 INTEGER,
       z1 CHARACTER(30),
       a1 INTEGER,
       k1 DECIMAL,       
       b1 DATE)
    PRIMARY INDEX (x1, y1),
    INDEX (z1);

Assume the following statistics have been collected on test1.

Statistics Test1 Table Column Set
single column
  • x1
  • y1
  • k1
single-column NUSI z1
multicolumn NUPI (x1, y1)

Example1

In this example, the system copies zeroed statistics for the following column and index sets.

    CREATE GLOBAL TEMPORARY TABLE t2 AS (SELECT *
                                         FROM test1) 
    WITH NO DATA AND STATISTICS;
The system copies these zeroed statistics … From this source table column set … To this target table column set … Because …
single column x1 x1 all the columns defined for target table t2 are exact analogs of all columns defined for source table test1.
y1 y1
k1 k1
none z1
single-column NUSI z1 none
multicolumn NUPI (x1, y1) (x1, y1)

Example 2

In this example, the system copies zeroed statistics for the following column and index sets.

    CREATE GLOBAL TEMPORARY TABLE t2 AS (SELECT *
                                         FROM test) 
    WITH NO DATA AND STATISTICS;
System copies these zeroed statistics From this source table column set To this target table column set Description
single column x x all the columns defined for target table t2 are analogs of all columns defined for source table test except that the NUSI on (x,y ) is not defined as an index on t2 , so its index statistics are copied as multicolumn statistics for t2.
y y
single-column NUPI a a
multicolumn (x, y, z) (x, y, z)
(a, b) (a, b)
none (x, y)
multicolumn NUSI (x, y) none

Example 3: Copying Zeroed Statistics for a Table

In this example, Vantage copies zeroed statistics for the source table t1 to the target table test.

    CREATE TABLE t1 AS 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 x all the columns defined for target table t1 are exact analogs of all columns defined for source table test.
y y
single-column NUPI a a
multicolumn
  • (a, b)
  • (x, y, z)
  • (a, b)
  • (x, y, z)
composite NUSI (x, y) (x, y)

Example4

In this example, Vantage copies no statistics because of the join condition on test and test1 specified in the subquery:

    CREATE TABLE t1 AS (SELECT *
                        FROM test, test1) 
    WITH NO DATA AND STATISTICS;