Example: Copying PARTITION Statistics - Teradata Vantage - Analytics Database

Vantage copies PARTITION statistics in the following scenarios.

When you copy PARTITION statistics, the statistics copied to the target table might not correctly represent the data in the target table because of differences in internal partition number mapping between the source and target tables. This is true even if the table definitions returned by a SHOW TABLE request are identical and the data is the same in both tables.

It is critical to understand that there is no way to guarantee that a target table created using a CREATE TABLE AS … WITH DATA AND STATISTICS request is identical to the source table from which its statistics are copied down to the level of internal partition numbers, and it is important to understand that even though the two tables might appear to be identical from comparing their definitions using the output of SHOW TABLE requests on the tables.

As a general rule, you should always recollect the PARTITION statistics for the target table when you copy them from a source table.

Consider this DDL definition for the examples in this set.

    CREATE SET TABLE test3, 
    NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL,
    CHECKSUM = DEFAULT (
      a INTEGER,
      b DATE,
      c INTEGER,
      e INTEGER)
    PRIMARY INDEX (c)
    PARTITION BY RANGE_N (e BETWEEN 1 AND 1000000 EACH 50000);

For the examples that follow, assume the following statistics are collected on test3.

Example1

In this example, Vantage copies all the single column, multicolumn, and index statistics as well as the single-column PARTITION statistics and the multicolumn PARTITION statistics for the column set (PARTITION, a).

    CREATE TABLE t8 AS 
    test3 
    WITH DATA AND STATISTICS;

Example2

In this example, Vantage does not copy PARTITION statistics because the target table columns are specified in a subquery; however, the system does copy the single-column and multicolumn statistics from test3 to t8.

    CREATE TABLE t8 AS (SELECT *
                        FROM test3) 
    WITH DATA AND STATISTICS;

Example3

In this example, Vantage does not copy PARTITION statistics because indexes and partitioning are not copied to the target table when any index definition for the target table is specified explicitly. However, the system does copy all single-column and multicolumn statistics from test3 to t8.

    CREATE TABLE t8 AS test3
    WITH DATA AND STATISTICS 
    INDEX(a, c);