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

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.

    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.

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;

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;

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;

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;