为了讲解直方图,我收集统计信息的时候是 method_opt => 'for all columns size skewonly'
正式的生产环境中,最好别用allcolumns方式收集直方图,因为all columns 几乎会对所有列都收集直方图信息
method_opt => 'for all columns size skewonly'
drop table p500 purge;
create table p500 as select * from dba_objects;
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'TEST'
and a.table_name = 'P500';
select owner, table_name name, object_type, stale_stats, last_analyzed
from dba_tab_statistics
where table_name in ('P500')
and owner = 'TEST'
and (stale_stats = 'YES' or last_analyzed is null);
BEGIN
搜集统计信息
最新推荐文章于 2022-04-19 20:28:22 发布