Oracle的基數(shù)與選擇性分析

創(chuàng)建一個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列的基數(shù)

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

查看列的基數(shù)和選擇性，可以使用如下腳本

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

我們查看test表的owner的基數(shù)和選擇性

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

count(distinct owner)列為基數(shù)

total_rows列為總行數(shù)

selectivity列為選擇性

在做SQL優(yōu)化的時候，不要急忙運行上面SQL，首先應(yīng)該檢查表的segment_size有多大，如果表的segment_size過大(比如超過SGA的buffer_cache)，你要考慮運行上面SQL

是否對你當前的系統(tǒng)有影響，如果是測試環(huán)境，無所謂，如果是生產(chǎn)環(huán)境，要小心謹慎。

--其實建議使用統(tǒng)計信息表（dba_tab_col_statistics 、dba_tables ）里的信息來查看選擇性和基數(shù)，這里注意我們首先要收集統(tǒng)計信息，否則返回的列是空值。

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');

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% 就屬于選擇性差

如果統(tǒng)計信息有可能不是最新的最好使用下面的語句

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

但是選擇性低的列也不一定不需要建索引要根據(jù)業(yè)務(wù)來比如有7W行記錄 SCOTT的有23行如果經(jīng)常根據(jù)SCOTT查要建立索引