Create and delete Histogram in Oracle

Histogram 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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.