# Density statistic calculation in case of histogram

From: Neeraj Bhatia <neeraj.dba_at_gmail.com>
Date: Mon, 6 Apr 2009 12:17:30 +0530
Message-ID: <a8fd4d730904052347h1d4a1cdcjb130283a6116006f_at_mail.gmail.com>

Hi,

Please tell me how density is calculated in case of histograms.

I've a table with very skewed data:

scott_at_ORADB10G> select col_skew, count(*) from tab_skew group by col_skew order by 1;

COL_SKEW COUNT(*)
---------- ----------

```         1         10
2         10
3         10
4         10
5         10
6         10
7         10
8         10
9         10
10       9910

```

10 rows selected.

PL/SQL procedure successfully completed.

• Creating width-based histograms, #buckets = #distinct values scott_at_ORADB10G> exec dbms_stats.gather_table_stats(user, 'TAB_SKEW', method_opt=>'FOR COLUMNS COL_SKEW size 10');

PL/SQL procedure successfully completed.

scott_at_ORADB10G> select * from dba_tab_col_statistics where table_name='TAB_SKEW' and column_name='COL_SKEW';

OWNER TABLE_NA COLUMN_NAME NUM_DISTINCT LOW_VA HIGH_V DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE AVG_COL_LEN HISTOGRAM

```------ -------- -------------------- ------------ ------ ------ ----------
```
---------- ----------- --------- ----------- --- --- -----------
```SCOTT  TAB_SKEW COL_SKEW                       10 C102   C10B

.00005          0          10 06-APR-09       10000 YES NO            3
```
FREQUENCY Question: How density is calculated here? Also it's equal to Selectivity of non-popular values.

scott_at_ORADB10G> exec dbms_stats.gather_table_stats(user, 'TAB_SKEW', method_opt=>'FOR COLUMNS COL_SKEW size 5');

PL/SQL procedure successfully completed.

• Creating Height-based histograms, #buckets < #distinct values scott_at_ORADB10G> select * from dba_tab_col_statistics where table_name='TAB_SKEW' and column_name='COL_SKEW';

OWNER TABLE_NA COLUMN_NAME NUM_DISTINCT LOW_VA HIGH_V DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE AVG_COL_LEN HISTOGRAM

```------ -------- -------------------- ------------ ------ ------ ----------
```
---------- ----------- --------- ----------- --- --- -----------
```SCOTT  TAB_SKEW COL_SKEW                       10 C102   C10B

.98209          0           5 06-APR-09       10000 YES NO            3
```
HEIGHT BALANCED Question: How density is calculated here and how selectivity will be estimated for popolar as well as non-popular values?

```--