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
----------------------------------------------------------------

@capture_on

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

select
  table_name, stats_update_time
from
  dba_tab_stats_history
where
  table_name = 'T1'
order by 2 desc
;
 
exec dbms_stats.restore_table_stats(user, 't1', -
      '&1');

@tab_stat t1

@capture_off





Comments