SYSFRAGDIST
The sysfragdist system catalog table stores fragment-level column statistics for fragmented tables and indexes. One row exists for each table fragment or index fragment.
Only columns in fragmented tables are described here. (For table-level column statistics, see the sysdistrib system catalog table.)
The sysfragdist table has the following columns.
| Column | Type | Explanation |
|---|---|---|
| tabid | INTEGER | Unique identifying code of table ( = systables.tabid) |
| fragid | INTEGER | Unique identifying code of fragment ( = sysfragments.partnum) |
| colno | SMALLINT | Unique identifying code of column ( = syscolumns.colno) |
| seqno | SMALLINT | Sequence number (for distributions that span multiple rows) |
| mode | CHAR(1) | UPDATE STATISTICS mode (H = high, or M = medium) |
| resolution | SMALLFLOAT | Average percentage of the sample in each bin |
| confidence | SMALLFLOAT | Estimated likelihood that a MEDIUM mode sample value is equivalent to an exact HIGH mode result |
| rowssampled | FLOAT | Number of rows in the sample |
| ustbuildduration | INTERVAL HOUR TO FRACTION(5) | Time spent to calculate the distribution for this column |
| constr_time | DATETIME YEAR TO FRACTION(5) | Time when the distribution was recorded |
| ustnrows | FLOAT | Rows in fragment when distribution was calculated. |
| minibinsize | FLOAT | For internal use only |
| nupdates | FLOAT | Number of updates to the table |
| ndeletes | FLOAT | Number of deletes to the table |
| ninserts | FLOAT | Number of inserts to the table |
| version | INTEGER | Reserved for future use |
| dbsnum | INTEGER | Unique identifying code of sbspace where encdist is stored |
| encdist | STAT | Encrypted fragment distribution |
The set of rows with a given combination of tabid, fragid, and colno values identifies the column statistics for that fragment of a table. These statistics can span multiple rows by using the seqno column for sequence numbering.
The mode, resolution and confidence values that are specified in the UPDATE STATISTICS MEDIUM or HIGH statement that calculate the column statistics for the fragment are recorded in the sysfragdist columns of the same names. To use existing fragment statistics to build table statistics, these three parameters should not change between UPDATE STATISTICS statements that reference the fragments of the same table. The only exception to this is that H mode fragmented statistics can be used to build M mode table statistics.
Column distribution statistics for the fragment are stored in the column encdist. The dbsnum column stores the identifying code of the smart blob space where the encdist object describing this fragment is stored. By default, the SBSPACENAME configuration parameter setting is the identifier of the sbspace whose identifying code is in the dbsnum column.
The following three columns record counts of how many DML operations modifying data rows were performed on the fragment 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 existed when distribution statistics were generated. When UPDATE STATISTICS runs in MEDIUM or HIGH mode against the fragmented table with fragment level statistics, the database server compares the stored values in these columns with the current values in the partition.
When 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 for the fragment. Column statistics for the fragment corresponding to the row in the sysfragdist table are not updated if the sum of the stored values differs from the sum of these current DML counter values for the partition page by less than the threshold specified by the setting of the STATCHANGE table attribute or of the STATCHANGE configuration parameter.