字段的density

DENSITY：密度
先看看字段的概况
SQL> select table_name,column_name,num_distinct,density from dba_tab_columns where table_name='DEF_LEN';


TABLE_NAME     COLUMN_NAME  NUM_DISTINCT  DENSITY
-------------------- -------------------- ------------ ----------
DEF_LEN     A     1 1
DEF_LEN     B     2       .5
查找资料，参考MOS (文档 ID 43041.1)
The density is expressed as a decimal number between 0 and 1. 
Values close to 1 indicate that this column is unselective
Values close to 0 indicate that this column is highly selective


The more selective a column, the less rows are likely to be returned by a 
query referencing this column in its predicate list. 


The column selectivity is part of the equation used to decide on the best 
path for a query to take to retrieve the data required in the most effective 
manner and hence impacts the final cost value for the query.


Density is calculated as follows:


Pre 7.3
~~~~~~~


    Density = 1 / Number of distinct NON null values


数据库7.3版本前，density是0到1的值，当density趋近于0时，也就是具有更高num_distinct值，具有较高的选择性。density是决定使用高效手段检索数据的最佳查询的重要参数




7.3+
~~~~
The Density calculation has been refined by the use of histograms. If 
you have created histograms on your columns we can now use the histogram
information to give more accurate information. Otherwise the Density is 
calculated as before. With histograms we can use information on
popular and non-popular values to determine the selectivity. 


  Density =  Number of non-popular values
               ----------------------------
                  total number of values


在7.3版本后，因为直方图的使用，密度计算方法作了改变。
如果创建了直方图可以给出更为准确的信息，如果没有那density还说以前的算法。直方图可以使用常用值和不常用值来决定选择性。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31461640/viewspace-2141288/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31461640/viewspace-2141288/