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 distributionReserved for future use

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.