Query statistics

The OceanBase Database optimizer provides a variety of views for you to query various types of statistics after statistics are collected.

Query statistics of indexes and tables

The following table describes the views that you can query for statistics of indexes and tables.

Key fields to note within these views include:

• NUM_ROWS: the total number of rows in the table or index.
• AVG_ROW_LEN: the average length of rows in the table.
• LAST_ANALYZED: the time when statistics were collected. A non-NULL value indicates that statistics of the table or index have already been collected.
• STATTYPE_LOCKED: indicates whether the user has been locked. A non-NULL value indicates that statistics of the table are locked.

Query statistics of columns

The following table describes the views that you can query for statistics of columns.

Key fields to note within these views include:

• NUM_DISTINCT: the number of distinct values (NDV) in the column.
• LOW_VALUE: the minimum value of the column.
• HIGH_VALUE: the maximum value of the column.
• DENSITY: the density of the column. This field is related to the histogram.
• NUM_NULLS: the number of NULL values in the column.
• NUM_BUCKETS: the number of histogram buckets.
• LAST_ANALYZED: the time when statistics were collected. A non-NULL value indicates that statistics of the column have already been collected.
• SAMPLE_SIZE: the number of data rows referenced by the statistics collection.
• AVG_COL_LEN: the average length of the column.
• HISTOGRAM: the type of the histogram. A non-NULL value indicates that the histograms of the column have been collected.

Query histogram statistics of columns

The following table describes the views that you can query for histogram statistics of columns.

Key fields to note within these views include:

• ENDPOINT_NUMBER: the total number of rows of the column accumulated by the current bucket.
• ENDPOINT_ACTUAL_VALUE: the printed literal corresponding to the end value of the current bucket.
• ENDPOINT_REPEAT_COUNT: the occurrence frequency of the end value of the current bucket.