Note
This view is introduced in V3.2.1.
Purpose
This view displays the column statistics and histograms of subpartitions in the partitioned tables owned by the current user.
Applicability
This view applies only to OceanBase Database in Oracle mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table. |
| SUBPARTITION_NAME | VARCHAR2(128) | NO | The name of the subpartition. |
| COLUMN_NAME | VARCHAR2(128) | NO | The name of the column. |
| NUM_DISTINCT | NUMBER | YES | The number of distinct values in the column. |
| LOW_VALUE | VARCHAR2(128) | YES | The minimum value of the column. |
| HIGH_VALUE | VARCHAR2(128) | YES | The maximum value of the column. |
| DENSITY | NUMBER | YES | The density of the column. |
| NUM_NULLS | NUMBER | YES | The number of NULL values in the column. |
| NUM_BUCKETS | NUMBER | YES | The number of buckets in the column histogram. |
| SAMPLE_SIZE | NUMBER | YES | The sample size during analysis. |
| LAST_ANALYZED | DATE | YES | The time of the last analysis. |
| GLOBAL_STATS | VARCHAR2(3) | YES | YES: statistics have been collected.NO: statistics have not been collected. |
| USER_STATS | VARCHAR2(3) | YES | Indicates whether the partition statistics are user-defined. |
| NOTES | VARCHAR2(80) | YES | Records some additional attributes. |
| AVG_COL_LEN | NUMBER | YES | The average length of the column. |
| HISTOGRAM | VARCHAR2(15) | YES | The histogram type. |
Sample query
View the column statistics of the subpartitions of the partitioned table T_SUBPART that the current user has access to.
obclient [SYS]> SELECT * FROM SYS.USER_SUBPART_COL_STATISTICS WHERE TABLE_NAME='T_SUBPART';
The query result is as follows:
+------------+-------------------+-------------+--------------+-----------+------------+-----------------------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
| TABLE_NAME | SUBPARTITION_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | SAMPLE_SIZE | LAST_ANALYZED | GLOBAL_STATS | USER_STATS | NOTES | AVG_COL_LEN | HISTOGRAM |
+------------+-------------------+-------------+--------------+-----------+------------+-----------------------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
| T_SUBPART | P0SSP0 | C3 | 434 | 2 | 1999 | .002304147465437788 | 0 | 5 | 829 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0SSP0 | C2 | 248 | 2 | 999 | .0040322580645161289 | 0 | 5 | 829 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0SSP0 | C1 | 844 | 2 | 9990 | .0011848341232227489 | 0 | 5 | 829 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0SSP1 | C3 | 723 | 0 | 1998 | .0013831258644536654 | 0 | 5 | 1141 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0SSP1 | C2 | 482 | 0 | 998 | .0020746887966804979 | 0 | 5 | 1141 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0SSP1 | C1 | 1144 | 1000 | 9998 | .00087412587412587413 | 0 | 5 | 1141 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0SSP2 | C3 | 372 | 3 | 1991 | .0026881720430107529 | 0 | 5 | 560 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0SSP2 | C2 | 235 | 3 | 992 | .0042553191489361703 | 0 | 5 | 560 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0SSP2 | C1 | 575 | 1014 | 9986 | .0017391304347826088 | 0 | 5 | 560 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP0 | C3 | 465 | 2 | 1999 | .0021505376344086021 | 0 | 5 | 1122 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP0 | C2 | 248 | 2 | 999 | .0040322580645161289 | 0 | 5 | 1122 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP0 | C1 | 1119 | 1002 | 9999 | .00089365504915102768 | 0 | 5 | 1122 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP1 | C3 | 980 | 0 | 1998 | .0010204081632653062 | 0 | 5 | 2714 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP1 | C2 | 517 | 0 | 998 | .0019342359767891683 | 0 | 5 | 2714 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP1 | C1 | 2804 | 0 | 9997 | .0003566333808844508 | 0 | 5 | 2714 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP2 | C3 | 479 | 3 | 1992 | .0020876826722338203 | 0 | 5 | 1130 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP2 | C2 | 251 | 3 | 992 | .0039840637450199202 | 0 | 5 | 1130 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1SSP2 | C1 | 1168 | 1005 | 9992 | .00085616438356164379 | 0 | 5 | 1130 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP0 | C3 | 339 | 2 | 1999 | .0029498525073746312 | 0 | 5 | 549 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP0 | C2 | 228 | 2 | 999 | .0043859649122807015 | 0 | 5 | 549 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP0 | C1 | 527 | 1042 | 9963 | .0018975332068311196 | 0 | 5 | 549 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP1 | C3 | 725 | 1 | 1998 | .0013793103448275861 | 0 | 5 | 1125 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP1 | C2 | 473 | 1 | 998 | .0021141649048625794 | 0 | 5 | 1125 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP1 | C1 | 1125 | 1004 | 9995 | .00088888888888888893 | 0 | 5 | 1125 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP2 | C3 | 445 | 3 | 1987 | .0022471910112359553 | 0 | 5 | 830 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP2 | C2 | 251 | 3 | 992 | .0039840637450199202 | 0 | 5 | 830 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2SSP2 | C1 | 795 | 3 | 9972 | .0012578616352201257 | 0 | 5 | 830 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
+------------+-------------------+-------------+--------------+-----------+------------+-----------------------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
27 rows in set
References
View the column statistics of subpartitions of partitioned tables that the current user can access: ALL_SUBPART_COL_STATISTICS
View the column statistics of subpartitions of all partitioned tables in the current tenant: DBA_SUBPART_COL_STATISTICS
View table-level column statistics by querying the following views:
View global column statistics by querying the following views:
View partition-level column statistics by querying the following views:
View table-level histogram statistics by querying the following views:
View partition-level histogram statistics by querying the following views:
View subpartition-level histogram statistics by querying the following views:
View index statistics by querying the following views:
For more information about how to collect statistics, see the following topics: