Oracle的基数与选择性分析

69次阅读
没有评论

共计 1998 个字符,预计需要花费 5 分钟才能阅读完成。

本篇内容主要讲解“Oracle 的基数与选择性分析”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让丸趣 TV 小编来带大家学习“Oracle 的基数与选择性分析”吧!

基数

  一个列中唯一键 (Distinct_keys) 的个数,如有一个 100W 行的表,性别列的基数为 2 (select distinct gender from test),主键列的基数为 100W(select distinct mid from test);

选择性

  基数 / 总行数所占的百分比,性别 2/100w * 100% 主键 100%   选择性越高 越有利于使用索引 20~30% 就算是比较高了

1、如何判断是一个索引创建的是好还是坏呢? 

  就看他的基数和选择性 如果基数大选择性大 那么使用索引就比较好

2、性别这个列使不使用索引?

要看情况:

从 OLTP 系统上来说在选择性低的列上创建索引肯定不适合的,基数 / 选择性高的列,适合建立 B -Tree 索引;

    在 OLAP 系统中基数低的列根据需求,有可能会建立 bitmap 索引

3、如何查看列的选择性和基数呢?

创建一个 test 测试表,

create table test as select * from dba_objects; 

create index idx_owner on test(owner); 

create index idx_object_name on test(object_name);

查看 owner 列和 object_name 列的基数

select count(distinct owner),count(distinct object_name) from test;

查看列的基数和选择性,可以使用如下脚本

select count(distinct column_name),count(*) total_rows,count(distinct column_name) / count(*) * 100 selectivity from table_name; 

我们查看 test 表的 owner 的基数和选择性

select ,count(*) total_rows,count(distinct owner) / count(*) * 100 selectivity from test;

count(distinct owner)列为基数

total_rows 列为总行数

selectivity 列为选择性

在做 SQL 优化的时候,不要急忙运行上面 SQL,首先应该检查表的 segment_size 有多大,如果表的 segment_size 过大(比如超过 SGA 的 buffer_cache),你要考虑运行上面 SQL

是否对你当前的系统有影响,如果是测试环境,无所谓,如果是生产环境,要小心谨慎。

– 其实建议使用统计信息表(dba_tab_col_statistics、dba_tables)里的信息来查看选择性和基数,这里注意我们首先要收集统计信息,否则返回的列是空值。

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

4、找出系统某个用户中不合理(选择性很低)的索引脚本

select a.OWNER,

a.INDEX_NAME,

a.TABLE_NAME,

a.DISTINCT_KEYS Cardinality,

a.NUM_ROWS,

round(a.DISTINCT_KEYS / NUM_ROWS * 100, 2) selectivity

from dba_ind_statistics a

where A.OWNER = upper(owner

selectivity 5 一般选择性小于 5% 就属于选择性差

如果统计信息有可能不是最新的最好使用下面的语句

select table_name,index_name,round(distinct_keys/num_rows * 100, 2) selectivity from user_indexes;

但是选择性低的列也不一定不需要建索引要根据业务来比如有 7W 行记录 SCOTT 的有 23 行如果经常根据 SCOTT 查要建立索引

到此,相信大家对“Oracle 的基数与选择性分析”有了更深的了解,不妨来实际操作一番吧!这里是丸趣 TV 网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-20发表,共计1998字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)