Note
This view is available starting with V3.2.1.
Purpose
ALL_PART_COL_STATISTICS displays the column statistics and histogram information of the partitions in the partitioned tables accessible to the current user.
Applicability
This view is available only in Oracle mode of OceanBase Database.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The user to which the column belongs. |
| 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 histogram of the column. |
| SAMPLE_SIZE | NUMBER | YES | The sample size used for analysis. |
| LAST_ANALYZED | DATE | YES | The last time when the statistics were collected. |
| GLOBAL_STATS | VARCHAR2(3) | YES | YES: Indicates that the statistics were directly collected.NO: Indicates that the statistics were derived from the subpartition statistics or that no statistics were collected. |
| USER_STATS | VARCHAR2(3) | YES | Indicates whether the partition statistics are user-defined. |
| NOTES | VARCHAR2(80) | YES | 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 partitioned table T_SUBPART accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_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 | SAMPLE_SIZE | LAST_ANALYZED | GLOBAL_STATS | USER_STATS | NOTES | AVG_COL_LEN | HISTOGRAM |
+-------+------------+----------------+-------------+--------------+-----------+------------+-----------------------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
| SYS | T_SUBPART | P2 | C1 | 2658 | 3 | 9995 | .00037622272385252068 | 0 | 5 | 2504 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1 | C1 | 4986 | 0 | 9999 | .00020056157240272763 | 0 | 5 | 4966 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0 | C1 | 2646 | 2 | 9998 | .0003779289493575208 | 0 | 5 | 2530 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2 | C2 | 965 | 1 | 999 | .0010362694300518134 | 0 | 5 | 2504 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1 | C2 | 1031 | 0 | 999 | .00096993210475266732 | 0 | 5 | 4966 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0 | C2 | 975 | 0 | 999 | .0010256410256410256 | 0 | 5 | 2530 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2 | C3 | 1498 | 1 | 1999 | .00066755674232309744 | 0 | 5 | 2504 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1 | C3 | 1933 | 0 | 1999 | .00051733057423693739 | 0 | 5 | 4966 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | 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 statistics of partitions in all partitioned tables of the current tenant, use the DBA_PART_COL_STATISTICS view.
To query the statistics of partitions in all partitioned tables of the current user, use the USER_PART_COL_STATISTICS view.
To query the column statistics at the table level, use the following views:
To query the column statistics at the global level, use the following views:
To query the column statistics at the subpartition level, use the following views:
To query the histogram statistics at the table level, use the following views:
To query the histogram statistics at the partition level, use the following views:
To query the histogram statistics at the subpartition level, use the following views:
To query the index statistics, use the following views:
For more information about how to collect statistics, see the following topics: