Displays the list of automated statistic definitions that are stored in the TDSTATS database.
Definition
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) . . . ;
Input Parameters
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. |
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 Teradata Vantage™ SQL External Routine Programming , B035-1147 .
Wildcard Characters
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 Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.
Displaying Statistics
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. 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. |
Output Parameters
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. 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:
|
Excluded | Possible values:
|
StatsState | Possible values:
|
StatsOrigin | Possible values:
|
StatsType | Type of statistics identified by the dictionary:
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 Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144 or Teradata Vantage™ Data Dictionary, B035-1092. |
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 Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144 or Teradata Vantage™ - Database Administration, B035-1093. |
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. |
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. |
ActiveStat | Possible values:
|
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. 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. |