How to force oracle 12c to create histograms

oracle-12cstatistics

I installed TPC-H on Oracle 12c and my goal is to get histograms on column groups (if anyone knows for sure that this is not possible anyway, please prove and I'll stop testing…). So I used 

select   dbms_stats.CREATE_EXTENDED_STATS(NULL,'H_LINEITEM','(l_shipdate, l_receiptdate)') 
from dual;

for creating extended statistics. Afterwards I gathered statistics:

exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'H_LINEITEM', OPTIONS=>'GATHER AUTO');

I also queried some stuff on the respective columns like

SELECT COUNT(*) 
FROM H_LINEITEM 
WHERE L_RECEIPTDATE > L_SHIPDATE + 5;

or

select l_shipdate, l_receiptdate, count(*) 
from h_lineitem 
group by (l_shipdate, l_receiptdate) 
order by 3 desc 
fetch first 10 rows only;

and gathered statistics again and again.
But everytime I look at the statistics with

select column_name, num_distinct, num_nulls, histogram 
from user_tab_col_statistics 
where table_name = 'H_LINEITEM'

there's not one histogram present.
Is there any parameter to change or do I need to write a whole script with lots of queries so that the optimizer starts considering making histograms?

Best Answer

A friend of mine told me today, that using the following command

begin
  DBMS_STATS.GATHER_TABLE_STATS (
    ownname => USER,
    tabname => 'H_LINEITEM',
    estimate_percent => 100,
    method_opt=>'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 SYS_STU3ZM$X#P01D3JFWNOWKQ7DD$'
  );
end;
/

where SYS_STU3ZM$X#P01D3JFWNOWKQ7DD$ is the automatically generated name of the column group,

one can manually create a histogram for a given column / column group.

A description can be found here.

Related Question