决定索引扫描成本的重要因素 clustering_factor

最新推荐文章于 2024-11-08 23:38:17 发布

转载最新推荐文章于 2024-11-08 23:38:17 发布 · 95 阅读

Clustering_factor是如何计算出来的
在索引的分析数据上clustering_factor是一个很重要的参数，表示的是索引和表之间的关系，因为，索引是按照一定的顺序排列的，但是，对于表来说是按照一种heap的形式存放，每一行可能分布在段上任何一个块上，所以要是通过索引来查找数据行的时候，就有可以一个索引块对应多个，甚至全部表的块，所以引入了clustering_factor这个参数来表示表上数据存放和索引之间的对应关系。这样CBO就可以根据这个参数来判断使用这个索引产生的cost是多少。
一般来说，如果这个表的排列是按照索引列的顺序存放数据的话，这个参数就应该和数据表上的块相类似。

该因子用于反映利用索引进行区间扫描的成本，说得可能有点抽象，我们做一个有趣的实验说明一下吧：

　　第一步，在Oracle中建立一个表Source，该表很简单，就两个字段，脚本如下：

　　create　table　Source
　　(
　　x　int　,
　　y　int
　　);

　　第二步，向Source里面插入100万条记录，其中x从1到1000000中按顺序取值，而y则是以随机数。

　　begin
　　for　i　in　1　..　1000000
　　loop
　　insert　into　source(x,y)　values　(　i　,　to_char(dbms_random.random,'99999999999999999')　);
　　end　loop;
　　end;
　　commit;

　　第三步，基于Source表的数据，再创建两张表tab01、tab02，并为这两个表的x字段添加索引，最后分析统计一下信息。

　　建表语句如下：

　　create　table　tab01　as　select　*　from　source;
　　create　table　tab02　as　select　*　from　source　order　by　y;
　　create　index　idx01　on　tab01(x);
　　create　index　idx02　on　tab02(x);
　　begin
　　dbms_stats.gather_table_stats(　user　,　'tab01'　,　cascade=>true　);
　　dbms_stats.gather_table_stats(　user　,　'tab02'　,　cascade=>true　);
　　end;

　　第四步，运行两个查询语句(将Autotrace打开)。

　　select　avg(y/(x+1))　from　tab01　where　x　between　10000　and　30000;
　　select　avg(y/(x+1))　from　tab02　where　x　between　10000　and　30000;

　　这两个查询查询非常简单，执行的速度也很快，我在普通PC机中的虚拟机(有点拗口)上面执行用了不到1秒就搞定了。

　　但是，这两个查询的执行计划，还有consistent gets的数字是不一样的。

第一个查询：

　　game@ora10g(oracle01)　SQL>　select　avg(y/(x+1))　from　tab01　where　x　between　10000　and　30000;
　　Elapsed:　00:00:00.02
　　--------------------------------------------------------------------------------------
　　|　Id　　|　Operation　　　　　　　　　　　　　　　　　　　　|　Name　　|　Rows　　|　Bytes　|　Cost　(%CPU)|　Time　　　　　|
　　--------------------------------------------------------------------------------------
　　|　　　0　|　SELECT　STATEMENT　　　　　　　　　　　　　|　　　　　　　|　　　　　1　|　　　　12　|　　　143　　　(1)|　00:00:02　|
　　|　　　1　|　　SORT　AGGREGATE　　　　　　　　　　　　　　|　　　　　　　|　　　　　1　|　　　　12　|　　　　　　　　　　　　|　　　　　　

　　　　|
　　|　　　2　|　　　TABLE　ACCESS　BY　INDEX　ROWID|　TAB01　|　19971　|　　　234K|　　　143　　　(1)|　00:00:02　|
　　|*　　3　|　　　　INDEX　RANGE　SCAN　　　　　　　　　　|　IDX01　|　20005　|　　　　　　　|　　　　48　　　(3)|　00:00:01　|　**********
　　--------------------------------------------------------------------------------------
　　Statistics
　　----------------------------------------------------------
　　0　　recursive　calls
　　0　　db　block　gets
　　116　　consistent　gets
　　0　　physical　reads

　　这两个查询查询非常简单，执行的速度也很快，我在普通PC机中的虚拟机(有点拗口)上面执行用了不到1秒就搞定了。

　　但是，这两个查询的执行计划，还有consistent gets的数字是不一样的。

　　第一个查询：

　从执行时间上来说，没看出多大的问题，但是从 consistent gets 来说，我们给Oracle发出了一个“错误”的提示。

　　为什么同样的数据同样的数据结构，却有如此大的差异呢?细心的你，可能已经发现我们的建表语句存在着一定的差异。

　　create　table　tab01　as　select　*　from　source;
　　create　table　tab02　as　select　*　from　source　order　by　y;

　　tab01中的行是按照Source表中行的顺序排列的，相邻的行几乎是在同一个数据块中，而tab02的行是按照y列排序的，也就是乱须的，简单的说tab01和tab02

虽然数据是相同的，但是物理组织上是不相同的。

　　在物理组织上的差异造成了这么大的性能差异令我非常惊讶，我们再看看两个索引的填充因子是什么?

　　执行以下查询：

　　select
　　idx.index_name,
　　tab.table_name,
　　tab.num_rows,
　　tab.blocks,
　　idx.clustering_factor
　　from
　　user_indexes　idx　inner　join　user_tables　tab
　　on　idx.table_name　=　tab.table_name
　　order　by　table_name;

　　我这里的结果是：

　　INDEX_NAME　　　　　　　　　　　TABLE_NAME　　　　　　　　　　　　　　　　　　　　　　　NUM_ROWS　　　　　BLOCKS　CLUSTERING_FACTOR
　　--------------------　------------------------------　----------　----------　-----------------
　　IDX01　　　　　　　　　　　　　　　　TAB01　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　995426　　　　　　　2459　　　　　　

　　　　　　　　4723
　　IDX02　　　　　　　　　　　　　　　　TAB02　　　　　　　　　　　　　　　　　　　　　　　　　　　　　1003471　　　　　　　2459　　　　　　

　　　　　　999596

　　当Clustering Factor的值越高，进行索引区间扫描的成本越高，物理组织上更加“零散”。
　　这个例子可以从某种程度上解析：“为什么同一份数据在不同机器上跑，性能不一样?”.

参考： http://space.itpub.net/?uid-9842-action-viewspace-itemid-324587

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

转载于:http://blog.itpub.net/35489/viewspace-687439/