How to check column statistics in Oracle

When we ran gather stats on the table, it generate table stats, index stats, and column stats. Column statistics are used in the optimizer’s selectivity calculations

Let’s see how to find it

How to check column statistics in Oracle

Column stats can be found from dba_tab_columns or dba_tab_col_statistics

SELECT column_name, num_distinct, density, num_nulls,
avg_col_len, num_buckets, histogram
FROM dba_tab_columns
WHERE table_name = '&table_name' and owner='&table_owner;

Here

num_distinct is the number of distinct values of the column
density is calculated as 1/num_distinct. It measures the selectivity estimate for the column
num_nulls is the number of null values in the column
avg_col_len is the average column length
num_buckets is the number of histograms bucket
the histogram is the type of histogram present on the column

The query from dba_tab_col_statistics will be

SELECT column_name, num_distinct, density, num_nulls,
avg_col_len, num_buckets, histogram
FROM dba_tab_col_statistics
WHERE table_name = '&table_name' and owner='&table_owner;

Another query that can be used

select table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, num_distinct, low_value, high_value,
density, avg_col_len, num_nulls, histogram, num_buckets, sample_size, last_analyzed
from dba_tab_columns
where table_name = '&table_name' and owner='&table_owner;

How to view column statistics for a schema

select table_name, column_name, data_type, data_length, data_precision, data_scale, nullable, num_distinct, low_value, high_value,
density, avg_col_len, num_nulls, histogram, num_buckets, sample_size, last_analyzed
from dba_tab_columns
where owner = '&schema_name'
order by 1,2;

I have given the dba_tab_columns example here but we can use any of these views
DBA_TAB_COLUMNS
USER_TAB_COLUMNS
ALL_TAB_COLUMNS

we can now view CDB_TAB_COLUMNS starting with 12c with the container database. In the root of a multitenant container database (CDB), CDB_* views can be used to obtain information about tables

Related Articles
How to check Index statistics in Oracle: In this post, we find out the index statistics, what is meaning of each of those statistics
how to check last gather stats on table in Oracle : Check out this post on How to check gather stats on a table,how to check last gather stats on the table in oracle