SYSDISTRIB
The sysdistrib system catalog table stores data-distribution information for the query optimizer to use. Data distributions provide detailed table and column information to the optimizer to improve the choice of execution paths of SELECT statements.
| Column | Type | Explanation |
|---|---|---|
| tabid | INTEGER | Code identifying the table from which data values were gathered |
| colno | SMALLINT | Column number in the source table |
| seqno | INTEGER | Ordinal number for multiple entries |
| constructed | DATETIME YEAR TO FRACTION(5) | Date when the data distribution was created |
| mode | CHAR(1) | Optimization level: M = Medium H = High |
| resolution | SMALLFLOAT | Specified in the UPDATE STATISTICS statement |
| confidence | SMALLFLOAT | Specified in the UPDATE STATISTICS statement |
| encdat | STAT | Statistics information |
| type | CHAR(1) | Type of statistics: A = encdat has ASCII-encoded histogram in fixed-length character field S = encdat has user-defined statistics |
| smplsize | SMALLFLOAT | A value greater than zero up to 1.0 indicating a proportion of the total rows in the table that UPDATE STATISTICS samples. Values greater than 1.0 indicate the actual number of rows used that UPDATE STATISTICS samples. A value of zero indicates that no sample size is specified. UPDATE STATISTICS HIGH always updates statistics for all rows. |
| rowssmpld | FLOAT | Number of rows in the sample |
| constr_time | DATETIME YEAR TO FRACTION(5) | Time when the distribution was recorded |
| ustnrows | FLOAT | Rows in fragment when distribution was calculated. |
| ustbuildduration | INTERVAL HOUR TO FRACTION(5) | Time spent calculating the distribution statistics for this column |
| nupdates | FLOAT | Number of updates to the table |
| ndeletes | FLOAT | Number of deletes to the table |
| ninserts | FLOAT | Number of inserts to the table |
Information is stored in the sysdistrib table when an UPDATE STATISTICS statement with mode MEDIUM or HIGH is executed for a table. (UPDATE STATISTICS LOW does not insert a value into the mode column.)
Only user informix can select the encdat column.
Each row in the sysdistrib system catalog table is keyed by the tabid and colno for which the statistics are collected.
For built-in data type columns, the type field is set to A. The encdat column stores an ASCII-encoded histogram that is broken down into multiple rows, each of which contains 256 bytes.
In HCL OneDB™, for columns of user-defined data types, the type field is set to S. The encdat column stores the statistics collected by the statcollect user-defined routine in multirepresentational form. Only one row is stored for each tabid and colno pair. A composite index on the tabid, colno, and seqno columns requires unique combinations of values.
The following three DML counter columns record counts of how many DML operations modifying data rows were performed on the table at the time of generation of column distribution statistics:
- UPDATE operations in nupdates
- DELETE operations in ndeletes
- and INSERT operations in ninserts
These counts can also include rows modified by MERGE statements.
These DML counter columns store the values of the counters from the server partition that exists when distribution statistics are generated. If the AUTO_STAT_MODE configuration parameter, or the AUTO_STAT_MODE session environment setting, or the AUTO keyword of the UPDATE STATISTICS statement has enabled selective updating of data distribution statistics, the ninserts, ndeletes, and ninserts values can affect whether UPDATE STATISTICS operations refresh existing data distribution statistics. When the UPDATE STATISTICS statement runs in MEDIUM or HIGH mode against the table, the database server compares the stored values in these columns with the current values in the partition. Column distribution statistics for the table are not updated if the sum of the stored values differs from the sum of these current sysdistrib DML counter values from the partition page by less than the threshold specified by the setting of the STATCHANGE table attribute or of the STATCHANGE configuration parameter.