-- <pre>
-- setup script for blog post on comparing stats
-- Author: Maria Colgan maria.colgan@oracle.com (@SQLMaria)

set pages 9999
set lines 150
set echo on
spool setup_for_compare.log

-- delete existing statistics
BEGIN
 dbms_stats.delete_table_stats('SH','SALES');
END;
/

-- Gather statistics with a manually specified sample size
BEGIN
 dbms_stats.gather_table_stats('SH','SALES', estimate_percent=>'0.000001');
END;
/

select INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR from user_indexes where table_name='SALES';

--drop the stats tab
BEGIN
 dbms_stats.drop_stat_table('SH','MY_STAT_TAB');
END;
/

-- Create a stat table to backup the stats 
BEGIN
 dbms_stats.create_stat_table('SH','MY_STAT_TAB');
END;
/

-- Copy statisitcs gathered with manually set sample size to MY_STAT_TAB
BEGIN
 dbms_stats.export_table_stats('SH','SALES',stattab=>'MY_STAT_TAB',statid=>'manually_set_sample_size');
END;
/

-- confirm statsitics are there

select distinct(statid) from my_stat_tab;

-- delete existing statistics
BEGIN
 dbms_stats.delete_table_stats('SH','SALES');
END;
/

-- Gather statistics with AUTO_SAMPLE_SIZE
BEGIN
 dbms_stats.gather_table_stats('SH','SALES');
END;
/

select INDEX_NAME, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR from user_indexes where table_name='SALES';

Select dbms_stats.diff_table_stats_in_stattab('SH','SALES', 'MY_STAT_TAB', statid1=>'MANUALLY_SET_SAMPLE_SIZE')
From   dual;

set long 99999999
set longchunksize 99999

Select *
From   table(dbms_stats.diff_table_stats_in_stattab('SH','SALES', 'MY_STAT_TAB', statid1=>'MANUALLY_SET_SAMPLE_SIZE'));
-- </pre>
