Note
This view is introduced since OceanBase Database V4.0.0.
Purpose
The oceanbase.DBA_PART_COL_STATISTICS view displays the column statistics and histograms of all partitioned tables of the database.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | varchar(128) | NO | The owner of the partitioned table. |
| TABLE_NAME | varchar(128) | NO | The name of the table. |
| PARTITION_NAME | varchar(128) | NO | The name of the table partition. |
| COLUMN_NAME | varchar(128) | NO | The name of the column. |
| NUM_DISTINCT | decimal(10,0) | NO | The number of distinct values in the column. |
| LOW_VALUE | varchar(128) | NO | The minimum value in the column. |
| HIGH_VALUE | varchar(128) | NO | The maximum value in the column. |
| DENSITY | decimal(10,0) | NO | The density of the column. If you can create a histogram on the COLUMN_NAME column, this column shows the selectivity of the values with zero or one occurrence in the histogram. It does not represent the selectivity of values with two or more occurrences. If COLUMN_NAME is not available on the histogram, the value of this column is 1 or NUM_DISTINCT. |
| NUM_NULLS | decimal(10,0) | NO | The number of NULLs in the column. |
| NUM_BUCKETS | decimal(10,0) | NO | The number of buckets in the histogram of the column. |
| LAST_ANALYZED | datetime(6) | NO | The date when column chromatography was analyzed the last time. |
| SAMPLE_SIZE | decimal(10,0) | NO | The sample size for column chromatography analysis. |
| GLOBAL_STATS | varchar(3) | NO | Indicates whether the statistics have been collected. Valid values:YES: indicates that statistics have been collected.NO: indicates that statistics have been summarized from subpartitions or have not been collected. |
| NOTES | varchar(80) | NO | The additional properties that describe the statistics. Valid values:INCREMENTAL: indicates that the column has a summary.INCREMENTAL(HLL): indicates that the column has a summary in the HyperLogLog (HLL) format.INCREMENTAL(SAMPLING): indicates that the column has a summary in an adaptive sampling format. You can check this column to determine whether the summary of the adaptive sampling format has been completely eliminated and properly cleared. |
| AVG_COL_LEN | decimal(10,0) | NO | The average column length, in bytes. |
| HISTOGRAM | varchar(15) | NO | The type of the histogram. Valid values: |
Sample query
Query the column statistics and histogram information of the first-level partitions of the partitioned table t_subpart in the current tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_PART_COL_STATISTICS WHERE TABLE_NAME='t_subpart';
The query result is as follows:
+-------+------------+----------------+-------------+--------------+-----------+------------+--------------+-----------+-------------+----------------------------+-------------+--------------+------------+-------+-------------+-----------+
| OWNER | TABLE_NAME | PARTITION_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | LAST_ANALYZED | SAMPLE_SIZE | GLOBAL_STATS | USER_STATS | NOTES | AVG_COL_LEN | HISTOGRAM |
+-------+------------+----------------+-------------+--------------+-----------+------------+--------------+-----------+-------------+----------------------------+-------------+--------------+------------+-------+-------------+-----------+
| test | t_subpart | p0 | c1 | 3540 | 0 | 9999 | 0.0002824859 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3334 | NO | NO | NULL | 20 | HYBRID |
| test | t_subpart | p0 | c2 | 1012 | 0 | 999 | 0.0009881423 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3334 | NO | NO | NULL | 20 | HYBRID |
| test | t_subpart | p0 | c3 | 2030 | 0 | 1999 | 0.0004926108 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3334 | NO | NO | NULL | 20 | HYBRID |
| test | t_subpart | p1 | c1 | 3308 | 1 | 9997 | 0.0003022975 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3333 | NO | NO | NULL | 20 | HYBRID |
| test | t_subpart | p1 | c2 | 1012 | 0 | 999 | 0.0009881423 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3333 | NO | NO | NULL | 20 | HYBRID |
| test | t_subpart | p1 | c3 | 2030 | 0 | 1999 | 0.0004926108 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3333 | NO | NO | NULL | 20 | HYBRID |
| test | t_subpart | p2 | c1 | 3230 | 2 | 9998 | 0.0003095975 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3333 | NO | NO | NULL | 20 | HYBRID |
| test | t_subpart | p2 | c2 | 1012 | 0 | 999 | 0.0009881423 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3333 | NO | NO | NULL | 20 | HYBRID |
| test | t_subpart | p2 | c3 | 2030 | 0 | 1999 | 0.0004926108 | 0 | 5 | 2025-03-17 15:30:30.152256 | 3333 | NO | NO | NULL | 20 | HYBRID |
+-------+------------+----------------+-------------+--------------+-----------+------------+--------------+-----------+-------------+----------------------------+-------------+--------------+------------+-------+-------------+-----------+
9 rows in set
References
Column statistics at the table level: DBA_TAB_STATISTICS
Column statistics at the global level: DBA_TAB_COL_STATISTICS
Column statistics at the subpartition level: DBA_SUBPART_COL_STATISTICS
Histogram statistics at the table level: DBA_TAB_HISTOGRAMS
Histogram statistics at the partition level: DBA_PART_HISTOGRAMS
Histogram statistics at the subpartition level: DBA_SUBPART_HISTOGRAMS
Index statistics: DBA_IND_STATISTICS
For more information about how to collect statistics, see the following topics: