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 make sure 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 copying an entire database on which statistics have been collected, DSA copies the statistics even if the copyStats attribute is false or not specified. To avoid this, use Teradata PT API or Teradata JDBC to copy the database.
- If copying a database using DSA, any statistics associated with the table or indexes in that database are copied by default.
- 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.
- Any new statistics (column, multi-column, or index) added after job creation but before job is run, is copied as part of the job.
- Data Mover uses the output from SHOW STATISTICS to copy statistics to the target table.