Using Histograms on Evenly Distributed Values

A quick demo ... here's a script ...

drop table demo_hist
/

create table demo_hist
as
select floor(rownum/5) rn,do.* from dba_objects do
/

begin
dbms_stats.gather_table_stats
(
ownname => user,
tabname => 'DEMO_HIST',
estimate_percent => 100,
method_opt => 'for columns OWNER size 254'
);
end;
/

select distinct column_name from user_tab_histograms
where table_name = 'DEMO_HIST'
/

Select min(rn),
max(rn),
count(distinct rn),
count(*)
from demo_hist
/

set autotrace on

select count(*) from demo_hist
where rn = 5
/

select count(*) from demo_hist
where rn between 1 and 100
/

begin
dbms_stats.gather_table_stats
(
ownname => user,
tabname => 'DEMO_HIST',
estimate_percent => 100,
method_opt => 'for columns RN size 254'
);
end;
/

select count(*) from demo_hist
where rn = 5
/

select count(*) from demo_hist
where rn between 1 and 100
/

When I run this I get a demonstration that with a histogram on the RN column the optimizer gets a much better cardinality estimate for the two Count(*)'s on RN = 5 and RN between 1 and 100.

But I'm not going to show you by how much because you need to go and run this script yourself ;)

Whether the results are significant in terms of getting a good execution plan, well this test doesn't demonstrate that. But I believe that it does. More of that damned instinct.

OK, if someone begs me then I'll post my results. But it'll have to be a real grovelling.