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 |
| LAST_ANALYZED | DATE | NO | The last time when the statistics were collected |
| SAMPLE_SIZE | NUMBER | NO | The sample size used for the statistics collection |
| GLOBAL_STATS | VARCHAR2(3) | NO |
|
| USER_STATS | VARCHAR2(3) | NO | Indicates whether the partition statistics were set by the user |
| NOTES | VARCHAR2(80) | NO | Records additional attributes |
| AVG_COL_LEN | NUMBER | NO | The average length of the column |
| HISTOGRAM | VARCHAR2(15) | NO | The histogram type |
| SCOPE | VARCHAR2(7) | NO | The effective scope of the statistics |
Sample query
Query the column statistics and histogram information of the T_SUBPART table in the current 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 view the column statistics of all tenants, see CDB_TAB_COL_STATISTICS.
To view the column statistics of tables accessible to the current user, see ALL_TAB_COL_STATISTICS.
To view the column statistics of tables owned by the current user, see USER_TAB_COL_STATISTICS.
To view the column statistics at the table level, query the following views:
To view the column statistics at the partition level, query the following views:
To view the column statistics at the subpartition level, query the following views:
To view the histogram statistics at the table level, query the following views:
To view the histogram statistics at the partition level, query the following views:
To view the histogram statistics at the subpartition level, query the following views:
To view the index statistics, query the following views:
For information about how to collect statistics, see the following topics: