87 lines
2.7 KiB
Plaintext
87 lines
2.7 KiB
Plaintext
# Tracking column histogram modifications by M.Houri
|
|
# https://hourim.wordpress.com/2020/08/06/historical-column-histogram/
|
|
|
|
|
|
create table T1 tablespace TS1 as
|
|
select rownum id, decode(mod(rownum,10),0,2,1) c_freq, nvl(blocks,999) c_hb
|
|
from dba_tables ;
|
|
|
|
update T1 set c_freq=3 where rownum<=10;
|
|
commit;
|
|
|
|
create index idx_freq on T1(C_FREQ) tablespace TS1;
|
|
create index idx_hb on T1(C_HB) tablespace TS1;
|
|
|
|
|
|
select c_freq,count(*) from T1 group by c_freq order by 2 desc;
|
|
|
|
|
|
exec dbms_stats.gather_table_stats (user, 'T1', method_opt=>'for all columns size 1');
|
|
|
|
col column_name for a20
|
|
|
|
select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram
|
|
from user_tab_col_statistics
|
|
where table_name='T1' and column_name='C_FREQ';
|
|
|
|
|
|
|
|
select /*+ GATHER_PLAN_STATISTICS */ * from T1 where C_FREQ=3;
|
|
|
|
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));
|
|
|
|
|
|
select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram
|
|
from user_tab_col_statistics
|
|
where table_name='T1' and column_name='C_HB';
|
|
|
|
select /*+ GATHER_PLAN_STATISTICS */ * from T1 where C_HB=999;
|
|
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));
|
|
|
|
|
|
---------------- FREQ
|
|
|
|
exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for columns C_FREQ size AUTO');
|
|
|
|
select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram
|
|
from user_tab_col_statistics
|
|
where table_name='T1' and column_name='C_FREQ';
|
|
|
|
select endpoint_value as column_value,
|
|
endpoint_number as cummulative_frequency,
|
|
endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) as frequency
|
|
from user_tab_histograms
|
|
where table_name = 'T1' and column_name = 'C_FREQ';
|
|
|
|
alter system flush shared_pool;
|
|
|
|
select /*+ GATHER_PLAN_STATISTICS */ * from T1 where C_FREQ=3;
|
|
|
|
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));
|
|
|
|
|
|
--------------- WEIGHT
|
|
|
|
exec dbms_stats.gather_table_stats(user,'T1', method_opt=>'for columns C_HB size 254');
|
|
|
|
select column_name,num_distinct,density,num_nulls,num_buckets,sample_size,histogram
|
|
from user_tab_col_statistics
|
|
where table_name='T1' and column_name='C_HB';
|
|
|
|
|
|
select endpoint_value as column_value,
|
|
endpoint_number as cummulative_frequency,
|
|
endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) as frequency
|
|
from user_tab_histograms
|
|
where table_name = 'T1' and column_name = 'C_HB';
|
|
|
|
|
|
|
|
create table T1 tablespace TS1 as
|
|
select rownum id, decode(mod(rownum,10),0,2,1) c_freq, nvl(blocks,999) c_hb
|
|
from dba_extents ;
|
|
|
|
update T1 set c_freq=3 where rownum<=10;
|
|
commit;
|
|
|