15.10 - AND STATISTICS - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
December 2015
Programming Reference

The statistics for the source table be copied to a new table based on its definition.

The system also sets up the appropriate statistical histograms in the dictionary for the target table and copies the statistics from the source table into them (see SQL Request and Transaction Processing, B035-1142).

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.

If you use a CREATE TABLE … AS … WITH DATA AND STATISTICS request to create a target table, the PARTITION statistics you copy from the source table are not valid if the internal partition numbers in the target table are different than the source table.

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.

If you specify WITH NO DATA with the AND STATISTICS option, Teradata Database sets up the appropriate statistical histograms for the target table, but does not populate them with the available statistical information. This state is referred to as zeroed statistics. It is important to collect statistics once the table is populated.