ヒストグラム統計

DB

ヒストグラム統計には実データが含まれる

 

 

(8.0.28)

drop table tab1;
create table tab1(col1 int,col2 varchar(100) );

insert into tab1 values(100,'AAA');
insert into tab1 values(200,'BBB');
insert into tab1 values(300,'CCC');
select * from tab1;

analyze table tab1;


-- ヒストグラム取得
ANALYZE TABLE tab1 UPDATE HISTOGRAM ON col1, col2 WITH 32 BUCKETS;

SELECT * FROM information_schema.column_statistics;
SELECT * FROM information_schema.column_statistics\G

 

-- ヒストグラム削除
ANALYZE TABLE tab1 DROP HISTOGRAM ON col1,col2;

 

 

(19c)

drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(100) );

insert into tab1 values(100,'AAA');
insert into tab1 values(200,'BBB');
insert into tab1 values(300,'CCC');
commit;
select * from tab1;

-- ヒストグラム取得
exec dbms_stats.gather_table_stats(user,'TAB1', method_opt =>'FOR ALL COLUMNS SIZE 100');

select TABLE_NAME||','||COLUMN_NAME||','||NUM_DISTINCT||','||LOW_VALUE||','||HIGH_VALUE
from user_tab_col_statistics
where table_name = 'TAB1'
order by TABLE_NAME,COLUMN_NAME;

select TABLE_NAME||','||COLUMN_NAME||','||ENDPOINT_NUMBER||','||ENDPOINT_VALUE||','||ENDPOINT_ACTUAL_VALUE||','||ENDPOINT_ACTUAL_VALUE_RAW||','||ENDPOINT_REPEAT_COUNT||','||SCOPE
from user_tab_histograms
where table_name = 'TAB1'
order by TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER;


-- ヒストグラム削除
exec dbms_stats.delete_column_stats(user,'TAB1','COL1',col_stat_type=>'HISTOGRAM');
exec dbms_stats.delete_column_stats(user,'TAB1','COL2',col_stat_type=>'HISTOGRAM');

 

(14)
pg_stats.histogram_bounds
列の値を満遍なく似たような数でグループに分配した値のリストです。


drop table tab1;
create table tab1(col1 int,col2 varchar(100) );

insert into tab1 values(100,'AAA');
insert into tab1 values(200,'BBB');
insert into tab1 values(300,'CCC');
select * from tab1;

analyze tab1;

select * from pg_stats where tablename='tab1';

 

 

(2019)


http://memorandom-nishi.hatenablog.jp/entry/2017/02/18/021834


drop table tab1;
create table tab1(col1 int not null ,col2 varchar(100) not null);

insert into tab1 values(100,'AAA');
insert into tab1 values(200,'BBB');
insert into tab1 values(300,'CCC');
select * from tab1;

alter table tab1 add constraint tab1pk primary key (col1);
create index ind12 on tab1(col2);


update statistics tab1;


DBCC SHOW_STATISTICS (tab1, tab1pk) WITH HISTOGRAM,NO_INFOMSGS
DBCC SHOW_STATISTICS (tab1, ind12) WITH HISTOGRAM,NO_INFOMSGS