Each row set contains a COLLECT STATISTICS request and related information for collecting the recommended statistics generated by a DUMP EXPLAIN or INSERT EXPLAIN request specified with a CHECK STATISTICS clause.
The following CREATE TABLE request defines the StatsRecs table.
CREATE SET TABLE StatsRecs (
QueryID INTEGER,
StatsID INTEGER,
DatabaseName VARCHAR128 CHARACTER SET UNICODE NOT CASESPECIFIC,
TableName VARCHAR128 CHARACTER SET UNICODE NOT CASESPECIFIC,
FieldID INTEGER,
FieldName VARCHAR128 CHARACTER SET UNICODE NOT CASESPECIFIC,
Level INTEGER,
StatsDDL VARCHAR(2500) CHARACTER SETUNICODE NOT CASESPECIFIC)
PRIMARY INDEX (QueryID, StatsID);
The following table defines the StatsRecs table attributes.
Attribute |
Description |
|||
QueryID |
|
|||
StatsID |
There are multiple StatsID values corresponding to a single QueryID for multicolumn statistics recommendations. In other words, a set of rows having the same value in StatsId represents all the columns for a given recommendation. |
|||
DatabaseName |
Name of the containing database for TableName. |
|||
TableName |
Name of the table in which FieldName is defined. |
|||
FieldID |
Unique identifier for FieldName within TableName. |
|||
FieldName |
Name of the column in the statistics recommendation. |
|||
Level |
A representation of the confidence the Optimizer has in the usefulness of the statistics recommendations it has generated for this query‑column set combination. The level is determined by a number of factors, including the following: This measure is designed to help you prioritize which statistics you want to collect, particularly in situations where you cannot afford to collect statistics on a long list of multicolumn recommendations or for different combinations of multicolumn recommendations. You can also aggregate levels to rank different recommendations. This recommendation is more likely to help the Optimizer to generate the least costly plan possible than any others, and you should always implement them unless you have very good reasons not to. Recommendations to collect single‑column and multicolumn statistics with all the columns are considered to be primary. Optional recommendations provide an alternative to collecting statistics on multiple unindexed columns. These recommendations can be useful when their columns are infrequently specified together as an equality condition within the given workload or when your site determines that it would be too costly to collect statistics on several different combinations of multicolumn recommendations, but unlike the case for Level 1 recommendations, you should not feel compelled to implement them. Optional recommendations are provided based on information collected from usable indexes and usable columns from the table described by the TableName column. |
|||
StatsDDL |
The DDL text of the COLLECT STATISTICS statement used to populate this row set of the table. For multicolumn statistics, the text of the DDL statement is stored in the row having the lowest FieldID value. In this case, the content of this column for the other rows is null. |