15.10 - SelectAutomatedStats - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Displays the list of automated statistic definitions that are stored in the TDSTATS database.

REPLACE PROCEDURE TDSTATS.SelectAutomatedStats
 (IN DatabaseName   VARCHAR(128) CHARACTER SET UNICODE,
 IN TableName     VARCHAR(128) CHARACTER SET UNICODE,
 IN ObjectListName VARCHAR(128) CHARACTER SET UNICODE)
           .
           .
           .
;
 

Parameter

Description

DatabaseName

Name of the database. DatabaseName limits the display to those statistics defined within the database you specify.

This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see “Usage Notes.”

TableName

Name of the table within the specified DatabaseName. TableName limits the display to those statistics for the table you specify within DatabaseName.

This input parameter may contain wildcard characters (%, _). For more information about wildcard characters, see “Usage Notes.”

ObjectListName

Name of the object list. ObjecListName allows you to specify a previously defined list of objects (that is, one or more database names or fully qualified table names where each name entry may optionally contain wildcard characters) whose statistics should be displayed.

For more information about this input parameter, see “Usage Notes” or “CreateObjectList” on page 603.

Returning Result Set

The output of this external stored procedure is in the form of a stored procedure dynamic result. That is, the external stored procedure can return result sets to the client application or to the caller of the external stored procedure (in addition to consuming the result sets itself) upon completion of the external stored procedure.

For more information about stored procedure dynamic result sets, see SQL External Routine Programming.

Wildcard Characters

The following table describes the wildcard characters that the names of some databases, tables, and objects contain.

 

Character

Description

% (PERCENT SIGN)

Represents any string of zero or more arbitrary characters. Any string of characters is acceptable as a replacement for the percent.

_ (LOW LINE)

Represents exactly one arbitrary character. Any single character is acceptable in the position in which the underscore character appears.

For more information on how to use these wildcard characters, see the SQL LIKE clause in SQL Functions, Operators, Expressions, and Predicates.

Displaying Statistics

Notice:

If you do not specify a value for the following input parameters, the operation is performed on all user created databases in the system.

 

To display ...

You must specify a value for ...

all statistics for a particular database

DatabaseName.

all statistics for a particular table

both DatabaseName and TableName.

a list of objects that cannot be specified by DatabaseName or TableName

ObjectListName that was previously created by the CreateObjectList and AddObjectListEntry external stored procedures.

Note: ObjectListName usage is mutually exclusive with DatabaseName and TableName. When you specify a value other than NULL for ObjectListName, DatabaseName and TableName must be NULL.

 

Parameter

Description

DatabaseName

Database in which the statistic is defined.

TableName

Table on which the statistic is defined.

ExpressionList

List of comma separated columns or expressions on which the statistic is defined.

IndexNumber

Dictionary assigned number of the index on which the statistic is defined as recorded in the DBC.StatsTbl.IndexNumber column.

Note: This input parameter is NULL if the statistic is not collected on an index.

IndexName

User assigned name for the index, if any.

StatsId

Dictionary assigned ID for the statistic as recorded in the DBC.StatsTbl.StatsId column.

StatsName

User assigned name for the statistic, if any.

SCOID

TDSTATS database assigned ID for the automated statistic.

ApprovedStat

Possible values:

  • Y means the statistic is approved for collection.
  • N means the statistic is unapproved for collection.
  • D means the statistic is disapproved by the user.
  • Excluded

    Possible values:

  • Y means the statistic is defined on an object that has become excluded since the initial automation. For more information, see “AddExcludedObject” on page 570.
  • N means the statistic is NULL.
  • StatsState

    Possible values:

  • M means the statistic is missing recommendations.
  • S means the statistic is stale.
  • C means the statistic is current.
  • StatsOrigin

    Possible values:

  • U means the statistic is imported from the user by an Automate-related external stored procedure.
  • A means the statistic is automatically recommended by the system.
  • StatsType

    Type of statistics identified by the dictionary:

  • B (Base table)
  • V (View)
  • Q (Query)
  • Note: V and Q statistic types are reserved for future use.

    LastCollectTimeStamp

    Time of when the statistic was last collected.

    LastEvaluationTimeStamp

    Time when the statistics was last submitted for collection by Automated Statistics Management. Depending on the SQL COLLECT STATISTICS THRESHOLD option in effect, the statistic may be recollected.

    For more information about the THRESHOLD option, see COLLECT STATISTICS statement in SQL Data Definition Language or Data Dictionary.

    SubmissionSkipCnt

    Number of consecutive runs where the statistic was not submitted for collection due to the time expiration or aborting.

    ThresholdSkipCnt

    Number of consecutive submissions where the statistic did not meet its defined threshold for recollection.

    For more information about the SQL COLLECT STATISTICS THRESHOLD option, see SQL Data Definition Language or Database Administration.

    UserImportance

    User assigned value for an entry that can be used to influence the priority of the statistic during the PrepCollect operation.

    For more information about this external stored procedure, see “PrepCollect” on page 546.

    SystemImportance

    System assigned value that is used in calculating the priority of the statistic during the PrepCollect operation.

    For more information on this external stored procedure, see “PrepCollect” on page 546.

    ActiveStat

    Possible values:

  • N means the statistic is marked inactive by the CleanupStats external stored procedure as a result of no observed usage by the Optimizer and is no longer incorporated by the PrepCollect external stored procedure.
  • Y means the statistic is actively used.
  • LastAccessedByOpt

    Last recorded statistics usage by query optimizer according to system-wide activity counters.

    QryUsageFrequency

    Number of logged queries observed using the statistic.

    MissingFrequency

    Number of queries observed wanting the missing statistic.

    EstPercentDataChange

    Estimated percent of data changed since the last collection as determined by the most recent call to the Analyzer-related external stored procedure or PrepCollect.

    For more information on this external stored procedure, see “PrepCollect” on page 546.

    Note: The EstPercentDataChange value is NULL when the StatsState column value is M or when the EstPercentDataChange value is not a reliable estimate.

    Age

    Number of days since the last collected statistic. For more information, see the LastCollectTimeStamp output column.

    Threshold

    Dictionary defined THRESHOLD setting for the statistic encoded as a text signature.

    The Threshold column can be defined with the Age and EstPercentDataChange settings. For details, see the EstPercentDataChange and Age columns.

    EstimateError

    Indicator that one or more queries observed have cardinality estimation inaccuracy involving the use or absence of this statistic.

    EstErrFrequency

    Number of queries observed with cardinality estimation inaccuracies.

    CreateUser

    User who originally automated the statistic.

    CreateTimeStamp

    Time in which the statistic was originally automated.

    LastAlterUser

    User who last altered the TDSTATS database stored data for the statistic.

    LastAlterTimeStamp

    Time in which the TDSTATS database data for this statistic was last altered.