Note
This view is available starting with V4.0.0.
Purpose
This view displays the column statistics and histogram information of all subpartitioned tables in the current tenant.
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 for the COLUMN_NAME column, this column displays the selectivity of values that span fewer than two endpoints in the histogram. It does not represent the selectivity of values that span two or more endpoints. If no histogram is available for the COLUMN_NAME column, the value of this column is 1/NUM_DISTINCT. |
| NUM_NULLS | decimal(10,0) | NO | The number of NULL values in the column. |
| NUM_BUCKETS | decimal(10,0) | NO | The number of buckets in the column histogram. |
| LAST_ANALYZED | datetime(6) | NO | The time when the column was last analyzed. |
| SAMPLE_SIZE | decimal(10,0) | NO | The sampling size when the column was analyzed. |
| GLOBAL_STATS | varchar(3) | NO | Indicates whether statistics have been collected: |
| USER_STATS | varchar(3) | NO | Indicates whether the statistics were directly input by the user: |
| NOTES | varchar(80) | NO | Describes some additional properties of the statistics. For example, if the value is INCREMENTAL, the global statistics are from the summary, that is, the global statistics are incrementally maintained. |
| AVG_COL_LEN | decimal(10,0) | NO | The average length of the column, in bytes. |
| HISTOGRAM | varchar(15) | NO | Indicates the type of the histogram: |
Sample query
Query the column statistics of the subpartitions of the t_subpart table 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
For more information about how to collect statistics, see the following topics:
