Note
This view is available starting with V3.2.1.
Purpose
This view displays the column statistics and histogram information of tables owned by the current user.
Applicability
This view is available only in Oracle mode.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| TABLE_NAME | VARCHAR2(128) | NO | The name of the table to which the column belongs. |
| 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. |
| LAST_ANALYZED | DATE | YES | The time when the last analysis was performed. |
| SAMPLE_SIZE | NUMBER | YES | The sample size used during analysis. |
| GLOBAL_STATS | VARCHAR2(3) | YES |
|
| 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 type of histogram. |
| SCOPE | VARCHAR2(7) | YES | The scope of the statistics. |
Sample query
Query the column statistics and histogram information of the T_SUBPART table owned by the current user.
obclient [SYS]> SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM FROM SYS.USER_TAB_COL_STATISTICS WHERE TABLE_NAME ='T_SUBPART' ORDER BY 1, 2, 3;
The query result is as follows:
+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | SAMPLE_SIZE | AVG_COL_LEN | HISTOGRAM |
+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+
| T_SUBPART | C1 | 10191 | 0 | 9999 | .000098125797272102829 | 0 | 5 | 10000 | 20 | HYBRID |
| T_SUBPART | C2 | 1031 | 0 | 999 | .00096993210475266732 | 0 | 5 | 10000 | 20 | HYBRID |
| T_SUBPART | C3 | 1973 | 0 | 1999 | .00050684237202230106 | 0 | 5 | 10000 | 20 | HYBRID |
+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+
3 rows in set
References
To view the statistics of tables accessible to the current user, see ALL_TAB_COL_STATISTICS.
To view the statistics of all tables in the current tenant, see DBA_TAB_COL_STATISTICS.
To query the column statistics of tables, view the following views:
To query the column statistics of partitions, view the following views:
To query the column statistics of subpartitions, view the following views:
To query the histogram statistics of tables, view the following views:
To query the histogram statistics of partitions, view the following views:
To query the histogram statistics of subpartitions, view the following views:
To query the index statistics, view the following views:
For more information about how to collect statistics, see the following topics: