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
