ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 决定索引扫描成本的重要因素 clustering_factor

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

原创 Linux操作系统 作者:tolywang 时间:2011-02-17 16:25:32 0 删除 编辑

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的数字是不一样的。

  第一个查询:


  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


 从执行时间上来说,没看出多大的问题,但是从 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13739325