About Copying Statistics - Teradata Data Mover

Teradata Data Mover User Guide

Product
Teradata Data Mover
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
kmo1482331935137.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem
Statistics provide information to Teradata Optimizer about the number of rows per value, and are used by the Teradata Database to evaluate and choose an optimal plan for accessing data. Using statistics improves the performance of complex queries and join. This is helpful in accessing a column or index with uneven value distribution. Statistics remain valid when a system is reconfigured.
Collected statistics are not automatically updated by the system. You are responsible for recollecting statistics to ensure Teradata Optimizer can make accurate decisions.

Requirements and Restrictions

A user must have SELECT access rights on dbc.IndexStats, dbc.ColumnStats, and dbc.MultiColumnStats views.

  • When using Teradata ARC to copy an entire database on which statistics have been collected, Teradata ARC copies the statistics even if the copyStats attribute is false or not specified. To avoid this situation, use Teradata PT API or Teradata JDBC to copy the database.
  • When overwriting join or hash indexes on the target, statistics that were collected on the target indexes are dropped.
  • The copyStats attribute is not displayed by default in the XML for tables, join indexes, and hash indexes that have had statistics collected. To copy statistics, enter the attribute in the XML manually.
  • Copying statistics from a Teradata Database 14.0 system to an earlier system is not supported and results in a create time error.
  • When copying statistics between Teradata Database 14.0 systems, you must have SELECT access rights on dbc.TableStatsV.
  • If any new statistics (column, multi-column, or index) are added after job creation but before job execution, those statistics will not be copied as part of job if the Teradata Database version is earlier than 14.0; however, those statistics will be copied if the Teradata Database is version 14.0 or later.
  • Statistics cannot be copied from a Teradata Database 14.10 system to an earlier version of Teradata Database with DBSControl field NoDot0Backdown set to true.
  • Statistics can be copied from a Teradata Database 14.00 system to a newer version of Teradata Database. It is recommended that statistics from the Teradata Database 14.00 are recollected, rather than copied, onto Teradata Databases 14.10 or newer. This provides full functionality of the version 6 statistics that are available on Teradata Databases 14.10 and newer.
  • Statistics cannot be copied or restored from a Teradata Database 15.00 or later to 14.00, regardless of the DBSControl field NoDot0Backdown value.
  • Statistics can be copied from Teradata Database 15.00 to Teradata Database 14.10 with DBSControl field NoDot0Backdown set to true.
  • Data Mover jobs should SKIP STATs copy for the following use cases, or the Data Mover job will fail:
    • When the source is Teradata Database 15.00 or later and the target system is Teradata Database 14.00, regardless of DBSControl field NoDot0Backdown value.
    • When the source is Teradata Database 14.10 with DBSControl field NoDot0Backdown set to true and the target system is an earlier version.
    Teradata Database 14.10 uses version 6 statistics when the DBSControl field NoDot0Backdown is set to true. When NoDot0Backdown is set to true, the database allows importing statistics from an older Teradata Database version, but does not allow exporting to an older version. Database systems upgraded to Teradata Database14.10 continue to use version 5 statistics, unless the default value for NoDot0Backdown is changed from false to true.
    Teradata Database versions 15.00 and later do not maintain the version 5 stats, regardless of the DBSControl field NoDot0Backdown value.