SYSTABLESTATISTICS System Table View

The SYSTABLESTATISTICS system view describes the statistics for tables within the current database.

SYS.SYSTABLESTATISTICS is a system view.

The following table shows the contents of the SYSTABLESTATISTICS system view.

SYSTABLESTATISTICS system view
Column Name Type Length Nullable Contents
SCHEMANAME VARCHAR 32672 YES The name of the schema
TABLENAME VARCHAR 32672 YES The name of the table
CONGLOMERATENAME VARCHAR 32672 YES The name of the table
TOTAL_ROW_COUNT BIGINT 19 YES The total number of rows in the table
AVG_ROW_COUNT BIGINT 19 YES The average number of rows in the table
TOTAL_SIZE BIGINT 19 YES The total size of the table
NUM_PARTITIONS BIGINT 19 YES The number of partitions1 for which statistics were collected.
AVG_PARTITION_SIZE BIGINT 19 YES The average size of a single partition1, in bytes.
ROW_WIDTH BIGINT 19 YES

The maximum average of the widths of rows in the table, across all partitions, in bytes.

Each partition records the average width of a single row. This value is the maximum of those averages across all partitions.

STATS_TYPE INTEGER 10 YES

The type of statistics, which is one of these values:

0 Full table (not sampled) statistics that reflect the unmerged partition values.
1 Sampled statistics that reflect the unmerged partition values.
2 Full table (not sampled) statistics that reflect the table values after all partitions have been merged.
3 Sampled statistics that reflect the table values after all partitions have been merged.

If this value is NULL, 0 is used.

SAMPLE_FRACTION DOUBLE 52 YES

The sampling percentage, expressed as 0.0 to 1.0,

  • If statsType=0 (full statistics), this value is not used, and is shown as 0.
  • If statsType=1, this value is the percentage or rows to be sampled. A value of 0 means no rows, and a value of 1 means all rows (full statistics).

1Currently, a partition is equivalent to a region. In the future, we may use a more finely-grained definition for partition.

See Also