Note
This view is available starting with V3.2.1.
Purpose
This view displays the column statistics and histogram information of the tables that the current user owns.
Applicability
This view is available starting with OceanBase Database in Oracle-compatible 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 in the column. |
| HIGH_VALUE | VARCHAR2(128) | YES | The maximum value in 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. |
| LAST_ANALYZED | DATE | YES | The time when the column was last analyzed. |
| SAMPLE_SIZE | NUMBER | YES | The sampling size used for analysis. |
| GLOBAL_STATS | VARCHAR2(3) | YES |
|
| USER_STATS | VARCHAR2(3) | YES | Indicates whether the partition statistics are user-defined. |
| NOTES | VARCHAR2(80) | YES | Stores additional attributes. |
| AVG_COL_LEN | NUMBER | YES | The average length of the column. |
| HISTOGRAM | VARCHAR2(15) | YES | The histogram type. |
| SCOPE | VARCHAR2(7) | YES | The scope of the statistics. |
Sample query
Query the column statistics and histogram information of the T_SUBPART table that the current user owns.
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 view column-level statistics of tables, query the following views:
To view column-level statistics of partitions, query the following views:
To view column-level statistics of subpartitions, query the following views:
To view histogram statistics of tables, query the following views:
To view histogram statistics of partitions, query the following views:
To view histogram statistics of subpartitions, query the following views:
To view index statistics, query the following views:
For information about how to collect statistics, see the following topics: