Note
This view is available starting with V3.2.1.
Purpose
This view displays the column statistics and histogram information of the subpartitions in the partitioned tables of the current tenant.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The user to whom the column belongs. |
| 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 | NO | The number of distinct values in the column. |
| LOW_VALUE | VARCHAR2(128) | NO | The minimum value of the column. |
| HIGH_VALUE | VARCHAR2(128) | NO | The maximum value of the column. |
| DENSITY | NUMBER | NO | The density of the column. |
| NUM_NULLS | NUMBER | NO | The number of NULL values in the column. |
| NUM_BUCKETS | NUMBER | NO | The number of buckets in the column histogram. |
| SAMPLE_SIZE | NUMBER | NO | The sample size used for analysis. |
| LAST_ANALYZED | DATE | NO | The time when the last analysis was performed. |
| GLOBAL_STATS | VARCHAR2(3) | NO |
|
| USER_STATS | VARCHAR2(3) | NO | Indicates whether the partition statistics are user-defined. |
| NOTES | VARCHAR2(80) | NO | Stores additional attributes. |
| AVG_COL_LEN | NUMBER | NO | The average length of the column. |
| HISTOGRAM | VARCHAR2(15) | NO | The type of the histogram. |
Sample query
Query the column statistics of the subpartitions in the T_SUBPART partitioned table of the current tenant.
obclient [SYS]> SELECT * FROM SYS.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 | SAMPLE_SIZE | LAST_ANALYZED | GLOBAL_STATS | USER_STATS | NOTES | AVG_COL_LEN | HISTOGRAM |
+-------+------------+-------------------+-------------+--------------+-----------+------------+-----------------------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
| SYS | T_SUBPART | P2SSP2 | C1 | 795 | 3 | 9972 | .0012578616352201257 | 0 | 5 | 830 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2SSP1 | C1 | 1125 | 1004 | 9995 | .00088888888888888893 | 0 | 5 | 1125 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2SSP0 | C1 | 527 | 1042 | 9963 | .0018975332068311196 | 0 | 5 | 549 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP2 | C1 | 1168 | 1005 | 9992 | .00085616438356164379 | 0 | 5 | 1130 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP1 | C1 | 2804 | 0 | 9997 | .0003566333808844508 | 0 | 5 | 2714 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP0 | C1 | 1119 | 1002 | 9999 | .00089365504915102768 | 0 | 5 | 1122 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP2 | C1 | 575 | 1014 | 9986 | .0017391304347826088 | 0 | 5 | 560 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP1 | C1 | 1144 | 1000 | 9998 | .00087412587412587413 | 0 | 5 | 1141 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP0 | C1 | 844 | 2 | 9990 | .0011848341232227489 | 0 | 5 | 829 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2SSP2 | C2 | 251 | 3 | 992 | .0039840637450199202 | 0 | 5 | 830 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2SSP1 | C2 | 473 | 1 | 998 | .0021141649048625794 | 0 | 5 | 1125 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2SSP0 | C2 | 228 | 2 | 999 | .0043859649122807015 | 0 | 5 | 549 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP2 | C2 | 251 | 3 | 992 | .0039840637450199202 | 0 | 5 | 1130 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP1 | C2 | 517 | 0 | 998 | .0019342359767891683 | 0 | 5 | 2714 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP0 | C2 | 248 | 2 | 999 | .0040322580645161289 | 0 | 5 | 1122 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP2 | C2 | 235 | 3 | 992 | .0042553191489361703 | 0 | 5 | 560 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP1 | C2 | 482 | 0 | 998 | .0020746887966804979 | 0 | 5 | 1141 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP0 | C2 | 248 | 2 | 999 | .0040322580645161289 | 0 | 5 | 829 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2SSP2 | C3 | 445 | 3 | 1987 | .0022471910112359553 | 0 | 5 | 830 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2SSP1 | C3 | 725 | 1 | 1998 | .0013793103448275861 | 0 | 5 | 1125 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P2SSP0 | C3 | 339 | 2 | 1999 | .0029498525073746312 | 0 | 5 | 549 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP2 | C3 | 479 | 3 | 1992 | .0020876826722338203 | 0 | 5 | 1130 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP1 | C3 | 980 | 0 | 1998 | .0010204081632653062 | 0 | 5 | 2714 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P1SSP0 | C3 | 465 | 2 | 1999 | .0021505376344086021 | 0 | 5 | 1122 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP2 | C3 | 372 | 3 | 1991 | .0026881720430107529 | 0 | 5 | 560 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP1 | C3 | 723 | 0 | 1998 | .0013831258644536654 | 0 | 5 | 1141 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
| SYS | T_SUBPART | P0SSP0 | C3 | 434 | 2 | 1999 | .002304147465437788 | 0 | 5 | 829 | 17-MAR-25 | NO | NO | NULL | 20 | HYBRID |
+-------+------------+-------------------+-------------+--------------+-----------+------------+-----------------------+-----------+-------------+-------------+---------------+--------------+------------+-------+-------------+-----------+
27 rows in set
References
To query the column statistics of subpartitions of all partitioned tables accessible to the current user, query the ALL_SUBPART_COL_STATISTICS view.
To query the column statistics of subpartitions of all partitioned tables owned by the current user, query the USER_SUBPART_COL_STATISTICS view.
To query the column statistics of tables, query the following views:
To query the column statistics of global tables, query the following views:
To query the column statistics of partitions, query the following views:
To query the histogram statistics of tables, query the following views:
To query the histogram statistics of partitions, query the following views:
To query the histogram statistics of subpartitions, query the following views:
To query the index statistics, query the following views:
For information about how to collect statistics, see the following topics: