The Table and Index editors have been updated to accommodate Collect Statistics options for the Teradata platform.
The following shows the new Collect Statistics and Using Sample options on the Datatype tab of the Table Editor.
The following shows the new Collect Statistics and Using Sample options on the Options tab of the Index Editor.
The following describes the purpose of these options and their effect on the SQL generated for the physical model:
Collect Statistics: (COLLECT STATISTICS) Collects demographic data for one or more columns of a table, computes a statistical profile of the collected data, and stores the profile in the data dictionary. Collecting full statistics involves scanning the base table, and sorting to determine the number of occurrences for each unique value. The Teradata Optimizer uses this profiling data when it generates its table access and join plans. Full statistics is generally recommended for relevant columns and indexes on tables with less than 100 rows per Access Module Processor (AMP).
This is an example of the SQL generated when Collect Statistics has been selected in the Table Editor for a column in the table.
COLLECT STATISTICS ON table_1 COLUMN column_1;
This is an example of the SQL generated when Collect Statistics has been selected in the Index Editor for an index in the table.
COLLECT STATISTICS ON table_1 INDEX unique_1;
Using Sample Collecting sampled full-table statistics is generally faster than collecting full table statistics, but may not produce the best table access and join plans. When you specify this option, Teradata will determine the appropriate percentage to the data to scan. The best choices for USING SAMPLE are columns or indexes that are unique or 95% unique.
This is an example of the SQL generated when Collect Statistics and Using Sample have been selected in the Tale Editor for a column in the table.
COLLECT STATISTICS USING SAMPLE ON table_1 COLUMN column_1;
This is an example of the SQL generated when Collect Statistics and Using Sample have been selected in the Index Editor for an index in the table.
COLLECT STATISTICS USING SAMPLE ON table_1 INDEX unique_1;