15.10 - Example: Copying Zeroed 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

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, Teradata Database copies zeroed statistics for the source table t1 to the target table test.

    CREATE TABLE t1 AS 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 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, Teradata Database 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;