SYSFRAGMENTS

The sysfragments system catalog table stores fragmentation information and LOW mode statistical distributions for individual fragments of tables and indexes. One row exists for each table fragment or index fragment.

The sysfragments table has the following columns.

Column Type Explanation
fragtype CHAR(1) Code indicating the type of fragmented object:
  • I = Original index fragment
  • T = Original table fragment
tabid INTEGER Unique identifying code of table
indexname VARCHAR(128) Name of index
colno INTEGER Identifying code of TEXT or BYTE column, or the upper limit on the number of rolling window fragments
partn INTEGER Identifying code of physical storage location
strategy CHAR(1) Code for type of fragment distribution strategy:
  • R = Round-robin distribution strategy
  • E = Expression-based distribution strategy
  • I = IN DBSPACE clause specifies a storage location as part of distribution strategy
  • N = raNge-iNterval (or rolliNg wiNdow) distribution strategy
  • L = List distribution strategy
  • T = Table-based distribution strategy
  • H = table is a subtable within a table Hierarchy
location CHAR(1) Reserved for future use; shows L for local
servername VARCHAR(128) Reserved for future use
evalpos INTEGER Position of fragment in the fragmentation list.

For fragmentation by INTERVAL, one of the following values that indicates the type of information in the exprtext field:

  • -1 = List of dbspaces for interval fragments
  • -2 = Interval value
  • -3 = Fragmentation key
  • -4 = Rolling window fragment

Fragmentation by LIST also uses the -3 value.
exprtext TEXT Expression for fragmentation strategy

For fragmentation by INTERVAL, LIST, or rolling window, provides the information corresponding to the value of the evalpos field.
exprbin BYTE Binary version of expression
exprarr BYTE Range-partitioning data to optimize expression in range-expression fragmentation strategy
flags INTEGER Used internally
dbspace VARCHAR(128) Name of dbspace storing this fragment
levels SMALLINT Number of B-tree index levels
npused FLOAT For table-fragmentation strategies: the number of data pages

For index-fragmentation strategies: the number of leaf pages

For rolling window tables: the units for the storage size limit in nrows
nrows FLOAT For tables: the number of rows in the fragment.

For indexes: the number of unique keys.

For rolling window tables: the upper limit on storage size in the purge policy.
clust FLOAT Degree of index clustering; smaller numbers correspond to greater clustering.
partition VARCHAR(128) Fragment name.This can match the name of the dbspace that stores the fragment, or can be an arbitrary name.
version SMALLINT Number that increments when fragment statistics is updated
nupdates FLOAT Number of updates to the fragment
ndeletes FLOAT Number of deletes to the fragment
ninserts FLOAT Number of inserts to the fragment

Every fragment has a row in this table. The evalpos and evaltext fields contain information about individual fragments.

Tables and indexes created with fragmentation by INTERVAL or LIST have additional rows containing information about the fragmentation strategy.

The strategy type T is used for attached indexes. (This is a fragmented index whose fragmentation strategy is the same as for the table fragmentation.)

For information about the nupdates, ndeletes, and ninserts columns, which in sysfragments tabulate DML operations on a table since the most recent recalculation of its distribution statistics, see the description of the three columns that have the same names in the SYSDISTRIB system catalog table.

In Informix®, a composite index on the fragtype, tabid, indexname, and evalpos columns allows duplicate values.