UpdateStatSampleSetting Stored Procedure | Teradata Vantage - 17.10 - UpdateStatSampleSetting - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

Records the user specified SAMPLE setting to include when preparing and executing the SQL COLLECT STATISTICS statements on the selected statistics or objects.

Syntax

REPLACE PROCEDURE TDSTATS.UpdateStatSampleSetting (
  IN  SCOID        BIGINT,
  IN  DatabaseName VARCHAR(128) CHARACTER SET UNICODE,
  IN  TableName    VARCHAR(128) CHARACTER SET UNICODE,
  IN  SampleType   CHAR(1) CHARACTER SET LATIN,
  IN  Percentage   INTEGER,
  OUT NumUpdated   INTEGER
)
  ...
;

Syntax Elements

SCOID
TDSTATS ID of an individual statistic whose SAMPLE settings are to be changed. For more information, see the SampleType input parameter.
DatabaseName
Name of the database. DatabaseName limits the new settings that will be applied to all statistics collected within the database you specify.
TableName
Name of the table within the specified DatabaseName. TableName limits the new settings to statistics collected on the table you specify within DatabaseName.
SampleType
Type of sample option change:
  • S means SYSTEM SAMPLE
  • U means SAMPLE <Percentage> PERCENT
  • N means no sampling
For more information about the COLLECT STATISTICS SAMPLE option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
Percentage
User supplied percentage.
This value is valid when SampleType is U. For details, see the SampleType input parameter.
NumUpdated
Number of statistics whose SAMPLE setting have been updated.
For more information about the COLLECT STATISTICS SAMPLE option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Usage Notes

The new collection settings will take effect during the next RunCollect involving the selected statistics. For more information about this external stored procedure, see RunCollect.

Example: Using UpdateStatSampleSetting

The following example shows how to specify a SampleType option (for example, SAMPLE 25 PERCENT) when collecting statistics on the individual statistic whose TDSTATS database ID is 102 on the Personnel.Employee table.

CALL TDSTATS.UpdateStatSamplingSetting(102, 'Personnel', 'Employee', 'U', 25, NumUpdated);
*** Procedure has been executed.
*** Total elapsed time was 1 second.
 NumUpdated
-----------
          1

Related Information

For more information about the COLLECT STATISTICS SAMPLE option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.