Query statistics

After statistics are collected, or when you want to verify whether a table has statistics, the OceanBase Database optimizer provides a variety of views for querying different types of statistics.

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 table statistics are locked. A non-NULL value means the table statistics 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 for this column were collected at some point in the past.
• SAMPLE_SIZE: the number of data rows referenced by the statistics collection.
• AVG_COL_LEN: the average length of the column.
• HISTOGRAM: the histogram type. A non-NULL value indicates that a histogram was collected for this column.

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 cumulative number of column rows up to and including the current bucket.
• ENDPOINT_ACTUAL_VALUE: the literal string representation of the end value of the current bucket.
• ENDPOINT_REPEAT_COUNT: the frequency of the end value of the current bucket.