Automatic Create Statistics Stored Procedures

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Automatic Create Statistics Stored Procedures

Until you have Teradata V13 Statistics Wizard Easy Feature available for your Teradata installation, here is a set of stored procedures that will generate and/or run collect statistics statements based on a set of index, column and referential integrity rules.

The Teradata V13 Statistics Wizard tool now has an "Easy" feature to generate and run collect statistics statements.  The Easy Feature of the Statistics Wizard automates the process of statistics collection/re-collection for particular database/tables. It does not require the capture of workloads via DBQL, but rather applies best practices based upon the data demographics information available in the database system. 

When Teradata V13 Statistics Wizard is not available, then these stored procedures may help.  The recommended use for these stored procedures if for the easy automation of statistics collection, and for use when initial statistics collection analysis is time constrained.  It is meant as "first out" suppliment to Teradata Statistics Wizard. 

You can either have the stored procedures automatically run the collect statistics statements, or have the stored procedures write the generated collect statistics statements to a table for later execution.  The latter option is useful when you wish to review and reduce the amount of statistics collected, or when SAMPLE option is desired for very large tables with evenly distributed index or column values.  These stored procedures have been tested against Teradata versions V2R5.1 through V13.

The "Run_Index_CS_SP" Stored Procedure will generate and run collect statistics statements for "collect statistics on databasename.tablename index (columnname,...);" statements for each index in the database and, if specified, tablename.

The "Run_Mul_Col_Only_CS_SP" Stored Procedure will generate and run collect statistics statements for "collect statistics on databasename.tablename columnname (columnname);" statements for each column included in multicolumn indices or referenced as a foreign key in the database and, if specified, tablename.

The "Create_Index_CS_SP" Stored Procedure will generate and write to a specified table collect statistics statements for "collect statistics on databasename.tablename index (columnname,...);" statements for each index in the database and, if specified, tablename.

The "Create_Mul_Col_Only_CS_SP" Stored Procedure will generate and write to a specified table collect statistics statements for "collect statistics on databasename.tablename columnname (columnname);"

statements for each column included in multicolumn indices or referenced as a foreign key in the database and, if specified, tablename.

Indexes included for generation in the Stored Procedure are (indextype):

• P (Nonpartitioned Primary)

• Q (Partitioned Primary)

• S (Secondary: NUSI and USI)

• K (primary key)

• V (value ordered secondary)

• I (ordering column of a composite secondary index)

These procedures use dbc.sysexecsql to submit dynamic SQL.

For more detailed information on the setup and use of the stored procedures, review the readme file.

3 REPLIES

Re: Automatic Create Statistics Stored Procedures

So where does one get the stored procedures?
Teradata Employee

Re: Automatic Create Statistics Stored Procedures

Top of the right-hand column under "Attachments"
Enthusiast

Re: Automatic Create Statistics Stored Procedures

This is great! I was going to create my own but this saves me a ton of time. One note for the next release, Run_Index_CS_SP & Run_Mul_Col_Only_CS_SP generate a 3722 error on running when connected in ANSI mode: "Only a COMMIT WORK or null statement is legal after a DDLStatement."