Example: Copying Statistics From a Source Table to an Identical Target Table - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

When you copy base table statistics from a source table to an identical target table, the database also copies the USING options from the source to the target by default. You cannot modify the USING options for the source table as part of a copy operation.

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.

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

The copy operation implicitly copies the object-level demographics from the source table to the target table.

The following COLLECT STATISTICS requests copy all the statistics from orders to orders_new, which has the same definition as orders. The database copies the corresponding USING options to orders_new as well as the statistics.

     COLLECT STATISTICS ON orders_new
     FROM orders;

     COLLECT STATISTICS USING SYSTEM SAMPLE
     COLUMN o_orderkey
     ON orders;