查看数据分布 & 查看列的基数和选择性

查看数据分布

select owner,count(*) from test group by owner order by 2 desc;

 

查看列的基数和选择性

统计信息不准确

（运行下面的SQL首先应该能检查该表的 segment_size 有多大，如果segment_size超过超过SGA的buffer_cache，会影响系统）

select count(distinct OWNER),
count(*) total_rows,
count(distinct OWNER) / count(*) * 100 selectivity
from SCOTT.TEST;

统计信息准确

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 = upper('SCOTT')
and a.table_name = upper('TEST')
and a.column_name = upper('OWNER');