Function of TableStatistics
Captures the statistics on a data sample for all the columns that are possible index candidates in the specified query.
TableStatistics also captures the detail statistics if you perform the SQL COLLECT STATISTICS (QCD Form) statement.
TableStatistics Table Definition
The following CREATE TABLE request defines the TableStatistics table:
CREATE TABLE TableStatistics ( MachineName VARCHAR(30) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL, TableName VARCHAR128 CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL, DatabaseName VARCHAR128 CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL, IndexName VARCHAR128 CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC, ColumnName VARCHAR128 CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL, CollectedTime TIMESTAMP(6) NOT NULL, SamplePercent FLOAT NOT NULL, IndexType CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, IndexID INTEGER, StatisticsInfo VARBYTE(16383), DataType SMALLINT, DataLength INTEGER, Attributes VARCHAR(256) CHARACTER SET LATIN NOT CASESPECIFIC, ModifiedTime TIMESTAMP(6), ModifiedStats VARBYTE(16383), QueryId INTEGER, FieldPosition BYTEINT, StatisticsID INTEGER) PRIMARY INDEX (MachineName, DatabaseName, TableName);
Attribute Definitions for TableStatistics
The following table defines the TableStatistics table attributes:
| Attribute | Definition |
|---|---|
| MachineName | The name of the system to which the table belongs. |
| TableName | Name of the table for which the row defines statistics. |
| DatabaseName | Name of the containing database or user for TableName. |
| IndexName | Name of the index, in case the index is named. Otherwise, IndexName is NULL. |
| ColumnName | Name of the column if the statistics are on a column. |
| CollectedTime | The timestamp value when the statistics were collected. |
| SamplePercent | The sample percentage of rows read to collect the statistics recorded in this row. |
| IndexType | Flag indicating whether the statistics represented by this row pertain to an index.
|
| IndexID | The unique identifier for the index to which the statistics represented by this row pertain.
|
| StatisticsInfo | Contains the column or index statistics. |
| DataType | Indicates the data type of the column. The value is the same that is returned in the data type column in the PrepInfo CLIv2 parcel. |
| DataLength | The maximum length of the column in bytes. |
| Attributes | Set null and reserved for future use. |
| ModifiedTime | If the statistics have been modified, indicates the timestamp value when the last modification occurred. |
| ModifiedStats | Contains user-modified column or index statistics. These statistics are used by the INITIATE INDEX ANALYSIS statement instead of the statistics in the StatisticsInfo column when you specify the USE MODIFIED STATISTICS option. |
| QueryID | The unique ID of the query. Set null when statistics are collected using COLLECT STATISTICS (QCD Form). |
| FieldPosition | Indicates the right-to-left position of the column within a composite index. Statistics are recorded in the row corresponding to FieldPosition 1 only. The value for other column positions are set null. |
| StatisticsID | Set to 1 if the demographics are captured by a COLLECT STATISTICS (QCD Form) or INSERT EXPLAIN statement. StatisticsID has different values if the demographics are imported rather than captured directly. |