NUM_DISTINCT and DENSITY in all_tab_col_statistics

Both columns are also in ALL_TAB_COLUMNS.

According to definition:
NUM_DISTINCT - number of distinct values in column
DENSITY - fraction of data that will be return by a query of type column=:a

So generally they should store the same data, as
DENSITY = 1 / NUM_DISTINCT

But it is not always the case. Please look at the example:

SQL> create table t1 as
2 select
3 rownum as ID,
4 trunc(dbms_random.value(1,20)) as VAL
5 from all_objects where rownum <= 1000;

Now we gather stats on this table, no histograms data:

SQL> begin
2 dbms_stats.gather_table_stats(user,'T1',estimate_percent=>null,method_opt=>'for all columns size 1');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from dba_tab_col_statistics where table_name='T1';

COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
ID 1000 .001
VAL 19 .052631579

Looks like it works how described above:

1/0.001 = 1000
1/19 = .052631579

But now let's change statistics - use histograms

SQL> begin
2 dbms_stats.gather_table_stats(user,'T1',estimate_percent=>null,method_opt=>'for all columns size 100');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from dba_tab_col_statistics where table_name='T1';

COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
ID 1000 .001
VAL 19 .0005

DENSITY for ID hasn't changed, but VAL density changed - now cbo has much more data to estimate density.

So which column cbo use to calculate cardinality (so what percentage of data query returns)? It depends whether you have histograms or no:
1. If there are no histograms, cbo assume that cardinality=num_rows/num_distincts
2. If we have histograms, cardinality=num_rows*density