Note
This view is available starting with V3.2.1.
Purpose
This view displays the column statistics and histogram information of the partitions in the partitioned tables of the current user.
Applicability
This view is available only in OceanBase Database in Oracle-compatible mode.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. |
| 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 sampling size during analysis. |
| LAST_ANALYZED | DATE | YES | The time when the last analysis was performed. |
| GLOBAL_STATS | VARCHAR2(3) | YES |
|
| USER_STATS | VARCHAR2(3) | YES | Indicates whether the partition statistics were set by the user. |
| 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
Query the column statistics and histogram information of the partitions in the T_SUBPART table of the current user.
obclient [SYS]> SELECT * FROM SYS.USER_PART_COL_STATISTICS WHERE TABLE_NAME='T_SUBPART';
The query result is as follows:
+------------+----------------+-------------+--------------+-----------+------------+-----------------------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
| TABLE_NAME | PARTITION_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 | P2 | C1 | 2658 | 3 | 9995 | .00037622272385252068 | 0 | 5 | 2504 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1 | C1 | 4986 | 0 | 9999 | .00020056157240272763 | 0 | 5 | 4966 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0 | C1 | 2646 | 2 | 9998 | .0003779289493575208 | 0 | 5 | 2530 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2 | C2 | 965 | 1 | 999 | .0010362694300518134 | 0 | 5 | 2504 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1 | C2 | 1031 | 0 | 999 | .00096993210475266732 | 0 | 5 | 4966 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0 | C2 | 975 | 0 | 999 | .0010256410256410256 | 0 | 5 | 2530 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P2 | C3 | 1498 | 1 | 1999 | .00066755674232309744 | 0 | 5 | 2504 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P1 | C3 | 1933 | 0 | 1999 | .00051733057423693739 | 0 | 5 | 4966 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| T_SUBPART | P0 | C3 | 1529 | 0 | 1999 | .00065402223675604975 | 0 | 5 | 2530 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
+------------+----------------+-------------+--------------+-----------+------------+-----------------------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
9 rows in set
References
To query the column statistics of partitions in the partitioned tables accessible to the current user, view ALL_PART_COL_STATISTICS.
To query the column statistics of partitions in all partitioned tables of the current tenant, view DBA_PART_COL_STATISTICS.
To query the column statistics at the table level, view the following views:
To query the column statistics at the global level, view the following views:
To query the column statistics at the subpartition level, view the following views:
To query the histogram statistics at the table level, view the following views:
To query the histogram statistics at the partition level, view the following views:
To query the histogram statistics at the subpartition level, view the following views:
To query the index statistics, view the following views:
For information about how to collect statistics, see the following topics: