Note
This view is available starting with V3.2.1.
Purpose
This view displays the column statistics and histogram information of all tables in the current tenant.
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 to which the column belongs |
| 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 histogram of the column |
| LAST_ANALYZED | DATE | NO | The time when the last analysis was performed |
| SAMPLE_SIZE | NUMBER | NO | The sample size used during analysis |
| GLOBAL_STATS | VARCHAR2(3) | NO |
|
| USER_STATS | VARCHAR2(3) | NO | Indicates whether the partition statistics are user-defined |
| NOTES | VARCHAR2(80) | NO | Records additional properties |
| AVG_COL_LEN | NUMBER | NO | The average length of the column |
| HISTOGRAM | VARCHAR2(15) | NO | The type of histogram |
| SCOPE | VARCHAR2(7) | NO | The scope of the statistics |
Sample query
Query the column statistics and histogram information of the T_SUBPART table in the current user tenant.
obclient [SYS]> SELECT OWNER, TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM FROM SYS.DBA_TAB_COL_STATISTICS WHERE TABLE_NAME ='T_SUBPART' ORDER BY 1, 2, 3;
The query result is as follows:
+-------+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+
| OWNER | TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | NUM_BUCKETS | SAMPLE_SIZE | AVG_COL_LEN | HISTOGRAM |
+-------+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+
| SYS | T_SUBPART | C1 | 10191 | 0 | 9999 | .000098125797272102829 | 0 | 5 | 10000 | 20 | HYBRID |
| SYS | T_SUBPART | C2 | 1031 | 0 | 999 | .00096993210475266732 | 0 | 5 | 10000 | 20 | HYBRID |
| SYS | T_SUBPART | C3 | 1973 | 0 | 1999 | .00050684237202230106 | 0 | 5 | 10000 | 20 | HYBRID |
+-------+------------+-------------+--------------+-----------+------------+------------------------+-----------+-------------+-------------+-------------+-----------+
3 rows in set
References
To query the column statistics of all tenants, see CDB_TAB_COL_STATISTICS.
To query the column statistics of tables accessible to the current user, see ALL_TAB_COL_STATISTICS.
To query the column statistics of tables owned by the current user, see USER_TAB_COL_STATISTICS.
To query the column statistics of tables at the table level, query the following views:
To query the column statistics of partitions, query the following views:
To query the column statistics of subpartitions, query the following views:
To query the histogram statistics of tables at the table level, 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: