Note
This view is available starting with V3.2.1.
Purpose
ALL_PART_COL_STATISTICS displays column statistics and histogram information for partitions of partitioned tables accessible to the current user.
Applicability
This view is available only in Oracle-compatible mode.
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 sampling size during analysis. |
| LAST_ANALYZED | DATE | YES | The last time when statistics were collected. |
| GLOBAL_STATS | VARCHAR2(3) | YES |
|
| 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 column statistics and histogram information for partitions of the T_SUBPART table 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 column statistics of partitions in all partitioned tables of the current tenant, execute the DBA_PART_COL_STATISTICS view.
To query the column statistics of partitions in all partitioned tables of the current user, execute the USER_PART_COL_STATISTICS view.
To query the column statistics of tables at the table level, execute the following views:
To query the column statistics at the global level, execute the following views:
To query the column statistics of subpartitions, execute the following views:
To query the histogram statistics of tables at the table level, execute the following views:
To query the histogram statistics of partitions, execute the following views:
To query the histogram statistics of subpartitions, execute the following views:
To query the index statistics, execute the following views:
For more information about how to collect statistics, see the following topics:
