Note
This view is available starting with V3.2.1.
Purpose
ALL_TAB_COL_STATISTICS displays the column statistics and histogram information of tables accessible to the current user.
Applicability
This view is available only in Oracle mode.
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 | 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 of the column. |
| LAST_ANALYZED | DATE | YES | The time when the column was last analyzed. |
| SAMPLE_SIZE | NUMBER | YES | The sample size used for analysis. |
| GLOBAL_STATS | VARCHAR2(3) | YES | YES: indicates that the statistics were directly collected or incrementally maintained.NO: other cases. |
| USER_STATS | VARCHAR2(3) | YES | Indicates whether the partition statistics were set by the user. |
| 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 the histogram. |
| SCOPE | VARCHAR2(7) | YES | The scope of the statistics. |
Sample query
Query the column statistics of the T_SUBPART table accessible to the current user.
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.ALL_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 tables in the current tenant, see DBA_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: