oceanbase.DBA_SUBPART_COL_STATISTICS|V4.3.5| docs|Distributed Database

oceanbase.DBA_SUBPART_COL_STATISTICS

Last Updated：2025-11-14 07:33:32 Updated

Note

This view is introduced since OceanBase Database V4.0.0.

Purpose

The oceanbase.DBA_SUBPART_COL_STATISTICS view displays the column statistics and histograms of all subpartitioned 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.
SUBPARTITION_NAME	varchar(128)	NO	The name of the subpartitioned table.
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 of the column.
HIGH_VALUE	varchar(128)	NO	The maximum value of the column.
DENSITY	decimal(10,0)	NO	The density of the column. If a histogram is available on COLUMN_NAME, this column shows the selectivity of values that span fewer than 2 endpoints in the histogram. It does not represent the selectivity of values spanning 2 or more endpoints. If a histogram is not available on COLUMN_NAME, the value of this column is 1/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 NO
USER_STATS	varchar(3)	NO	Indicates whether the statistics are directly entered by the user. Valid values: YES NO
NOTES	varchar(80)	NO	The additional properties that describe the statistics. For example, if the value is `INCREMENTAL`, the global statistics come from the summary, that is, the global statistics are maintained incrementally.
AVG_COL_LEN	decimal(10,0)	NO	The average column length, in bytes.
HISTOGRAM	varchar(15)	NO	The type of the histogram. Valid values: NONE FREQUENCY HEIGHT BALANCED HYBRID TOP-FREQUENCY

Sample query

Query the column statistics of the subpartitions of the partitioned table t_subpart in the current tenant.

obclient [oceanbase]> SELECT * FROM oceanbase.DBA_SUBPART_COL_STATISTICS WHERE TABLE_NAME='t_subpart';

The query result is as follows:

+-------+------------+-------------------+-------------+--------------+-----------+------------+--------------+-----------+-------------+----------------------------+-------------+--------------+------------+-------+-------------+-----------+
| OWNER | TABLE_NAME | SUBPARTITION_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  | p0ssp0            | c1          |         1313 | 0         | 9999       | 0.0007616146 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1336 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p0ssp0            | c2          |          332 | 0         | 999        | 0.0030120482 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1336 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p0ssp0            | c3          |          630 | 0         | 1999       | 0.0015873016 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1336 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p0ssp1            | c1          |         1001 | 2001      | 8997       | 0.0009990010 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p0ssp1            | c2          |          349 | 1         | 997        | 0.0028653295 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p0ssp1            | c3          |          691 | 1         | 1997       | 0.0014471780 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p0ssp2            | c1          |          996 | 1002      | 7998       | 0.0010040161 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p0ssp2            | c2          |          338 | 2         | 998        | 0.0029585799 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p0ssp2            | c3          |          678 | 2         | 1998       | 0.0014749263 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp0            | c1          |         1025 | 1000      | 7999       | 0.0009756098 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1002 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp0            | c2          |          332 | 0         | 999        | 0.0030120482 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1002 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp0            | c3          |          630 | 0         | 1999       | 0.0015873016 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1002 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp1            | c1          |         1357 | 1         | 9997       | 0.0007369197 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1332 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp1            | c2          |          349 | 1         | 997        | 0.0028653295 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1332 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp1            | c3          |          691 | 1         | 1997       | 0.0014471780 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1332 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp2            | c1          |          965 | 2002      | 8998       | 0.0010362694 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp2            | c2          |          338 | 2         | 998        | 0.0029585799 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p1ssp2            | c3          |          678 | 2         | 1998       | 0.0014749263 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp0            | c1          |         1007 | 2000      | 8999       | 0.0009930487 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1002 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp0            | c2          |          332 | 0         | 999        | 0.0030120482 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1002 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp0            | c3          |          630 | 0         | 1999       | 0.0015873016 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1002 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp1            | c1          |         1047 | 1001      | 7997       | 0.0009551098 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp1            | c2          |          349 | 1         | 997        | 0.0028653295 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp1            | c3          |          691 | 1         | 1997       | 0.0014471780 |         0 |           5 | 2025-03-17 15:30:30.152256 |         999 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp2            | c1          |         1298 | 2         | 9998       | 0.0007704160 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1332 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp2            | c2          |          338 | 2         | 998        | 0.0029585799 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1332 | NO           | NO         | NULL  |          20 | HYBRID    |
| test  | t_subpart  | p2ssp2            | c3          |          678 | 2         | 1998       | 0.0014749263 |         0 |           5 | 2025-03-17 15:30:30.152256 |        1332 | NO           | NO         | NULL  |          20 | HYBRID    |
+-------+------------+-------------------+-------------+--------------+-----------+------------+--------------+-----------+-------------+----------------------------+-------------+--------------+------------+-------+-------------+-----------+
27 rows in set

References

DBA_TAB_STATISTICS: View column statistics at the table level.
DBA_TAB_COL_STATISTICS: View column statistics at the global level.
DBA_PART_COL_STATISTICS: View column statistics at the partition level.
DBA_TAB_HISTOGRAMS: View histogram statistics at the table level.
DBA_PART_HISTOGRAMS: View histogram statistics at the partition level.
DBA_SUBPART_HISTOGRAMS: View histogram statistics at the subpartition level.
DBA_IND_STATISTICS: View index statistics.
For more information about how to collect statistics, see the following topics:
- Manually collect statistics
- Automatically collect statistics