16.00 - Collect Statistics Wizard - Teradata Studio

Teradata Studio User Guide

Teradata Studio
March 2017
User Guide

The Collect Statistics wizard automatically generates and runs the collect statistics SQL on selected columns, multi-column, and index collection objects. The dialog also allows you to edit the various collect statistics SQL parameters before running the SQL on the collection objects. By default the wizard only generates index-style collect statistics SQL which is the most recommended syntax to be used for Teradata Database version 14.0 and above.

You can launch the dialog from the Collect Statistics view by clicking Collect.

These options are available for collecting statistics on columns, multi-column, and indexes. You can click individual cells in the grid report to edit these values:
Option Description
Collection Objects Displays the columns or index objects on which the statistics are collected. The collection objects are displayed using databasename.tablename.column or databasename.tablename.index format.
Existing Statistics Displays a check mark when the collection objects already have statistics defined on them
Partitioned Automatically selected if the column is partitioned. Select or clear the check box to modify the partitioned column setting before running collect statistics SQL.
Name The name of a Statistic. After a name is defined, the name can be used in other operations such as DROP and SHOW STATISTICS. The naming of statistics is particularly helpful for multi-column statistics.
Max Value Length Statistics Histogram records values such as min, max, mode and biased values. The Max Value Length refers to the maximum value length that can be used to build these values. If the value length is larger than the system-determined or specified maximum, it gets truncated. Note that in prior releases of the Teradata Database 14.0, the maximum value length could not exceed 16 bytes. Increase the maximum value length for columns that require more detailed information in the histogram to improve single-table predicate selectivity estimations. However, this should be done selectively as needed as it increases the size of the histogram which can increase the query optimization time.
Max Intervals Maximum number of intervals of a histogram can be customized using the Max Interval option. If this option is not specified, the system uses a default maximum number of intervals which is defined as 250. A greater number of intervals increases the granularity of the data in the histogram and helps to get better single-table and join selectivity estimations for non-uniform data. However, this should be done selectively as needed (for columns involved in a predicate exhibiting over or under estimations) as it increases the size of the histogram which can increase the query optimization time.
Sampling Allows users to customize sample percentage for different collection objects. This makes system to scan a user-specified percentage of table rows. "n" denotes the sample size of type integer or decimal which can range from 2 to 100. A sample size of 100 is equivalent to collecting full statistics.
Threshold Allows users to skip statistics recollection based on amount of data change and/or on the age of the statistics (if it is below the threshold in effect). The statistics recollection is done if the age of the statistics is greater than or equal to the number of days specified or/and if the percentage of change in the statistics exceeds the specified threshold percentage.
These options are available for adding and removing collection objects:
Option Description
Remove By default all collection objects are marked for statistics collection. User can select any of the collection objects and click on remove to deselect collection objects.
Add After any statistics objects are cleared from the collection, you can add them again by clicking Add. This action opens a dialog to choose statistics objects that could be added to the collection objects again.
Multi-Column Ordering Changes the column ordering for the multi-column statistics objects. You can select any of the multi-column collection objects and click Multi-Column Ordering. This opens a Modify Column Ordering dialog where you can use the Up and Down buttons to change the ordering.
These options are available for previewing the collect statistics SQLs and running them:
Option Description
Next Previews the SQL that is generated automatically for collecting statistics on the collection objects.
Finish Runs the SQL that are generated automatically for collecting statistics on the collection objects.