Tag Archives: column Stats

Create and delete Histogram( Column Stats) in Oracle

Histogram or Column Stats in Oracle

Histogram used by SQL Query optimizer to make better execution plan.

A Histogram is a column statistic that provides more detailed information about the data distribution in a table column.

By Default optimizer use the uniform distribution of rows across the distinct value of columns.

Suppose if table has 3000 rows and one column Country has India has 2500 rows, USA has 300 rows and England has 200 rows. By default without histogram, Optimizer considers 1000 rows each for India, USA and England. To make and provide more relevant data to optimizer for choose better plan for SQL Queries we use histogram.

Col column_name for a30
Select column_name , notes , histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

COLUMN_NAME     NOTES           HISTOGRAM
------------    -------------   -------------
OWNER                           NONE
Elapsed: 00:00:00.01


For Creation of histogram on column you need to gather the stats as follows

Gather Stats with histogram
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','K1',OPTIONS=>'GATHER AUTO');

Select column_name , notes , histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

COLUMN_NAME      NOTES          HISTOGRAM
------------     -----------    -------------
OWNER                           FREQUENCY

 
Delete the Histogram:

Exec  dbms_stats.delete_column_stats(ownname=>'',
tabname=>'K1',colname=>'OWNER',col_stat_type=>'HISTOGRAM')

Select column_name,notes,histogram from user_tab_col_statistics where table_name = 'K1' and column_name = 'OWNER';

COLUMN_NAME        NOTES       HISTOGRAM
---------------    ---------   -------------
OWNER                          NONE