2 基数与选择性--优化主题系列

基数与选择性

    基数(Cardinality)列唯一键(Distinct_keys)的数量，比如性别，该列只有男女之分，所以这一列基数是2。主键列的基数等于行数。注：NULL值一般不算在基数里面。

    选择性(Selectivity)列唯一键(Distinct_Keys)与行数(Num_Rows)的比值。

 

基数和选择性这两个概念非常重要，在OLTP系统中，基数/选择性高的列，适合建立B-Tree索引，选择性低的列不适合建立索引。在OLAP环境中，基数低的列根据需求，可能会建立bitmap索引。

 

提问：某个表有1000W条数据（例子也许极端，请参考）

有个列只有男和女

男有500W条数据女有500W条数据

我要查询 where 性别='男'

是不是要返回50%的数据

那我现在再提问

假如男有1W条数据女有999W条数据

我要查询 where 性别='男'

此时是不是该创建索引？？

 

我们在进行SQL调优的时候，可以用下面SQL去查看列真实的基数和选择性

select count(distinct column_name),

       count(*) total_rows,

       count(distinct column_name)/ count(*) * 100 selectivity

  from table_name;

 

在做SQL优化的时候，不要急忙运行上面SQL，首先应该检查表的segment_size有多大，如果表的segment_size过大(比如超过SGA的buffer_cache)，你要考虑运行上面SQL是否对你当前的系统有影响，如果是测试环境，无所谓，如果是生产环境，要小心谨慎。

我这里强调真实二字，是因为查看基数和选择性的时候还有另外的方法，你也可以用下面的脚本查看

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('&owner')

   and a.table_name = upper('&table_name')

   and a.column_name = upper('&column_name');

 

如果统计信息准确，可以用上面SQL查看，如果统计信息不准确，就利用第一个SQL查看。