# 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.

**sysdistrib**table has the following columns.

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.