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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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.

Statistics Test3 Table Column Set
single column
  • a
  • b
single-column index on PPI NUPI c
single-column PARTITION PARTITION
composite NUPI (a, c)
composite PARTITION (PARTITION, a)

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;
Vantage copies these statistics … From this source table column set … To this target table column set … Because …
single column a a the system copies all columns from source table test3 to target table t8 with the same column names and properties and without using a subquery.
b b
single-column index on PPI NUPI c c
single-column PARTITION PARTITION PARTITION
composite NUPI (a, c) (a, c)
composite PARTITION (PARTITION, a) (PARTITION, a)

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;
Vantage copies these statistics … From this source table column set … To this target table column set … Because …
single column a a the columns in source table test3 and target table t8 are identical, so the system copies their single-column statistics.
b b
single-column index on PPI NUPI c c
composite NUPI (a, c) (a, c) the columns in source table test3 and target table t8 are identical, so the system copies their multicolumn statistics.
single-column PARTITION PARTITION none the system does not copy PARTITION statistics, either single-column or as part of a composite, if the target table columns are specified in a subquery.
composite PARTITION (PARTITION, a) none

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);
Vantage copies these statistics … From this source table column set … To this target table column set … Because …
single column a a the columns in source table test3 and target table t8 are identical, so the system copies their single-column statistics.
b b
single-column index on PPI NUPI c c
composite NUPI (a, c) (a, c) the columns in source table test3 and target table t8 are identical, so the system copies their multicolumn statistics.
single-column PARTITION PARTITION none the system does not copy indexes or PARTITION statistics if any index is defined explicitly for the target table.
composite PARTITION (PARTITION, a) none