Online Contents

04. diff_restore_table_stats.sql

-- @name: diff_restore_table_stats
-- @author: dion cho
-- @description: test for comparing table stats and restoring table stats


drop table t1 purge;
create table t1(c1 int, c2 int);

insert into t1
select mod(level, 100), level
from dual
connect by level <= 1000

exec dbms_stats.gather_table_stats(user, 't1');

@tab_stat t1

-- export stat table
exec dbms_stats.create_stat_table(user, 't1_stat');
exec dbms_stats.export_table_stats(user, 't1', null, 't1_stat');

-- modify data a lot!!
insert into t1
select mod(level, 2), level
from dual
connect by level <= 10000

-- with histogram
exec dbms_stats.gather_table_stats(user, 't1', -
    method_opt=>'for columns c1 size 254');

@tab_stat t1
-- compare stats
set long 100000

select * from
table(dbms_stats.diff_table_stats_in_stattab(user, 't1', 't1_stat'));

-- let's restore
exec dbms_stats.import_table_stats(user, 't1', null, 't1_stat');

-- or

  table_name, stats_update_time
  table_name = 'T1'
order by 2 desc
exec dbms_stats.restore_table_stats(user, 't1', -

@tab_stat t1