深入研究B树索引(五)

转载 2013年12月04日 14:12:22

5.     重建B树索引

5.1 如何重建B树索引

重建索引有两种方法:一种是最简单的,删除原索引,然后重建;第二种是使用ALTER INDEX … REBUILD

命令对索引进行重建。第二种方式是从oracle 7.3.3版本开始引入的,从而使得用户在重建索引时不必删除原索引再重新CREATE INDEX了。ALTER INDEX … REBUILD相对CREATE INDEX有以下好处:

1)  它使用原索引的叶子节点作为新索引的数据来源。我们知道,原索引的叶子节点的数据块通常都要比表里的数据块要少很多,因此进行的I/O就会减少;同时,由于原索引的叶子节点里的索引条目已经排序了,因此在重建索引的过程中,所做的排序工作也要少的多。

2)  自从oracle 8.1.6以来,ALTER INDEX … REBUILD命令可以添加ONLINE短语。这使得在重建索引的过程中,用户可以继续对原来的索引进行修改,也就是说可以继续对表进行DML操作。

而同时,ALTER INDEX … REBUILD与CREATE INDEX也有很多相同之处:

1)  它们都可以通过添加PARALLEL提示进行并行处理。

2)  它们都可以通过添加NOLOGGING短语,使得重建索引的过程中产生最少的重做条目(redo entry)。

3)  自从oracle 8.1.5以来,它们都可以田间COMPUTE STATISTICS短语,从而在重建索引的过程中,就生成CBO所需要的统计信息,这样就避免了索引创建完毕以后再次运行analyze或dbms_stats来收集统计信息。

当我们重建索引以后,在物理上所能获得的好处就是能够减少索引所占的空间大小(特别是能够减少叶子

节点的数量)。而索引大小减小以后,又能带来以下若干好处:

1)  CBO对于索引的使用可能会产生一个较小的成本值,从而在执行计划中选择使用索引。

2)  使用索引扫描的查询扫描的物理索引块会减少,从而提高效率。

3)  由于需要缓存的索引块减少了,从而让出了内存以供其他组件使用。

尽管重建索引具有一定的好处,但是盲目的认为重建索引能够解决很多问题也是不正确的。比如我见过一

个生产系统,每隔一个月就要重建所有的索引(而且我相信,很多生产系统可能都会这么做),其中包括一些100GB的大表。为了完成重建所有的索引,往往需要把这些工作分散到多个晚上进行。事实上,这是一个7×24的系统,仅重建索引一项任务就消耗了非常多的系统资源。但是每隔一段时间就重建索引有意义吗?这里就有一些关于重建索引的很流行的说法,主要包括:

1)  如果索引的层级超过X(X通常是3)级以后需要通过重建索引来降低其级别。

2)  如果经常删除索引键值,则需要定时重建索引来收回这些被删除的空间。

3)  如果索引的clustering_factor很高,则需要重建索引来降低该值。

4)  定期重建索引能够提高性能。

对于第一点来说,我们在前面已经知道,B树索引是一棵在高度上平衡的树,所以重建索引基本不可能降

低其级别,除非是极特殊的情况导致该索引有非常大量的碎片,导致B树索引“虚高”,那么这实际又来到第二点上(因为碎片通常都是由于删除引起的)。实际上,对于第一和第二点,我们应该通过运行ALTER INDEX … REBUILD命令以后检查indest_stats.pct_used字段来判断是否有必要重建索引。

5.2 重建B树索引对于clustering_factor的影响

而对于clustering_factor来说,它是用来比较索引的顺序程度与表的杂乱排序程度的一个度量。Oracle在计算某个clustering_factor时,会对每个索引键值查找对应到表的数据,在查找的过程中,会跟踪从一个表的数据块跳转到另外一个数据块的次数(当然,它不可能真的这么做,源代码里只是简单的扫描索引,从而获得ROWID,然后从这些ROWID获得表的数据块的地址)。每一次跳转时,有个计数器就会增加,最终该计数器的值就是clustering_factor。下图四描述了这个原理。

                                                                             图四             

       在上图四中,我们有一个表,该表有4个数据块,以及20条记录。在列N1上有一个索引,上图中的每个小黑点就表示一个索引条目。列N1的值如图所示。而N1的索引的叶子节点包含的值为:A、B、C、D、E、F。如果oracle开始扫描索引的底部,叶子节点包含的第一个N1值为A,那么根据该值可以知道对应的ROWID位于第一个数据块的第三行里,所以我们的计数器增加1。同时,A值还对应第二个数据块的第四行,由于跳转到了不同的数据块上,所以计数器再加1。同样的,在处理B时,可以知道对应第一个数据块的第二行,由于我们从第二个数据块跳转到了第一个数据块,所以计数器再加1。同时,B值还对应了第一个数据块的第五行,由于我们这里没有发生跳转,所以计数器不用加1。

在上面的图里,在表的每一行的下面都放了一个数字,它用来显示计数器跳转到该行时对应的值。当我们处理完索引的最后一个值时,我们在数据块上一共跳转了十次,所以该索引的clustering_factor为10。

注意第二个数据块,clustering_factor为8出现了4次。因为在索引里N1为E所对应的4个索引条目都指向了同一个数据块。从而使得clustering_factor不再增长。同样的现象出现在第三个数据块中,它包含三条记录,它们的值都是C,对应的clustering_factor都是6。

从clustering_factor的计算方法上可以看出,我们可以知道它的最小值就等于表所含有的数据块的数量;而最大值就是表所含有的记录的总行数。很明显,clustering_factor越小越好,越小说明通过索引查找表里的数据行时需要访问的表的数据块越少。

我们来看一个例子,来说明重建索引对于减小clustering_factor没有用处。首先我们创建一个测试表:

SQL> create table clustfact_test(id number,name varchar2(10));

SQL> create index idx_clustfact_test on clustfact_test(id);

然后,我们插入十万条记录。

SQL> begin

  2            for i in 1..100000 loop

  3                    insert into clustfact_test values(mod(i,200),to_char(i));

  4            end loop;

  5            commit;

  6  end;

  7  /

因为使用了mod的关系,最终数据在表里排列的形式为:

0,1,2,3,4,5,…,197,198,199,0,1,2,3,…, 197,198,199,0,1,2,3,…, 197,198,199,0,1,2,3,…

       接下来,我们分析表。

SQL> exec dbms_stats.gather_table_stats(user,'clustfact_test',cascade=>true);

       这个时候,我们来看看该索引的clustering_factor。

SQL> select num_rows, blocks from user_tables where table_name = 'CLUSTFACT_TEST';

  NUM_ROWS     BLOCKS

---------- ----------

    100000        202

SQL> select num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,

  2  clustering_factor from user_indexes where index_name = 'IDX_CLUSTFACT_TEST';

  NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

---------- ------------- ----------------------- ----------------------- -----------------

    100000           200                       1                     198             39613

       从上面的avg_data_blocks_per_key的值为198可以知道,每个键值平均分布在198个数据块里,而整个表也就202个数据块。这也就是说,要获取某个键值的所有记录,几乎每次都需要访问所有的数据块。从这里已经可以猜测到clustering_factor会非常大。事实上,该值近4万,也说明该索引并不会很有效。

       我们来看看下面这句SQL语句的执行计划。

SQL> select count(name) from clufac_test where id = 100;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=32 Card=1 Bytes=9)

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'CLUFAC_TEST' (Cost=32 Card=500 Bytes=4500)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        205  consistent gets

……

       很明显,CBO弃用了索引,而使用了全表扫描。这实际上已经说明由于索引的clustering_factor过高,导致通过索引获取数据时跳转的数据块过多,成本过高,因此直接使用全表扫描的成本会更低。

       这时我们来重建索引看看会对clustering_factor产生什么影响。从下面的测试中可以看到,没有任何影响。

SQL> alter index idx_clustfact_test rebuild;

SQL> select num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,

  2  clustering_factor from user_indexes where index_name = 'IDX_CLUSTFACT_TEST';

  NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

---------- ------------- ----------------------- ----------------------- -----------------

    100000           200                       1                     198             39613

       那么当我们将表里的数据按照id的顺序(也就是索引的排列顺序)重建时,该SQL语句会如何执行?

SQL> create table clustfact_test_temp as select * from clustfact_test order by id;

SQL> truncate table clustfact_test;

SQL> insert into clustfact_test select * from clustfact_test_temp;

SQL> exec dbms_stats.gather_table_stats(user,'clustfact_test',cascade=>true);

SQL> select num_rows, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key,

  2  clustering_factor from user_indexes where index_name = 'IDX_CLUSTFACT_TEST';

  NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

---------- ------------- ----------------------- ----------------------- -----------------

    100000           200                       1                       1               198

       很明显的,这时的索引里每个键值只分布在1个数据块里,同时clustering_factor也已经降低到了198。这时再次执行相同的查询语句时,CBO将会选择索引,同时可以看到consistent gets也从205降到了5。

SQL> select count(name) from clustfact_test where id = 100;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=9)

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'CLUSTFACT_TEST' (Cost=2 Card=500 Bytes=4500)

   3    2       INDEX (RANGE SCAN) OF 'IDX_CLUSTFACT_TEST' (NON-UNIQUE) (Cost=1 Card=500)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          5  consistent gets

……

       所以我们可以得出结论,如果仅仅是为了降低索引的clustering_factor而重建索引没有任何意义。降低clustering_factor的关键在于重建表里的数据。只有将表里的数据按照索引列排序以后,才能切实有效的降低clustering_factor。但是如果某个表存在多个索引的时候,需要仔细决定应该选择哪一个索引列来重建表。

相关文章推荐

深入研究B树索引(五)续

5.3 重建B树索引对于查询性能的影响        最后我们来看一下重建索引对于性能的提高到底会有什么作用。假设我们有一个表,该表具有1百万条记录,占用了100000个数据块。而在该表上存在一个索...

深入研究B树索引

  • 2011年11月09日 15:41
  • 359KB
  • 下载

深入研究B树索引(一)

深入研究B树索引(一) 上一篇 / 下一篇  2008-05-27 13:03:41 / 个人分类:工作技术 查看( 14381 ) / 评论( 61 ) / 评分( 146 / 82 ) ...

深入研究B树索引(四)续

4.2 B树索引的对于删除(DELETE)的管理        上面介绍了有关插入键值时索引的管理机制,那么对于删除键值时会怎么样呢? 在介绍删除索引键值的机制之前,先介绍与索引相关的一个比较重要...

深入研究B树索引(二)

2.     B树索引的内部结构 我们可以使用如下方式将B树索引转储成树状结构的形式而呈现出来: alter session set events 'immediate trace name t...

深入研究B树索引(三、四)

3.     B树索引的访问 我们已经知道了B树索引的体系结构,那么当oracle需要访问索引里的某个索引条目时,oracle是如何找 到该索引条目所在的数据块的呢?        当orac...

bitmap索引的深入研究(自我改版)

bitmap索引的深入研究 上一篇 / 下一篇  2008-06-10 17:28:21 / 个人分类:工作技术 查看( 952 ) / 评论( 9 ) / 评分( 30 / 1 ) 位图...
  • haiross
  • haiross
  • 2013年10月08日 15:52
  • 715

cocos2d-x box2d物理引擎深入研究 第一篇之旋转关节详解(b2RevoluteJoint)

对于旋转关节场常见的包括如下: 滚轮或滚筒链条或悬桥(使用多个旋转连接器)破布娃娃的关节转门,弹射器,杠杆 创建旋转关节 创建旋转关节首先设置b2RevoluteJointDef属性,然后用...

cocos2d-x box2d物理引擎深入研究 第一篇之鼠标关节详解(b2MouseJoint)

在testbed例子中,使用鼠标关节操作刚体,他尝试在刚体上驱动一个点,拖向当前的鼠标位置,在旋转方面没有任何限制. 鼠标关节定义有目标点(target point),最大力矩(maxinum ...

bitmap索引的深入研究

位图(bitmap)索引是另外一种索引类型,它的组织形式与B树索引相同,也是一棵平衡树。与B树索引的区别在于叶子节点里存放索引条目的方式不同。从前面我们知道,B树索引的叶子节点里,对于表里的每个数据行...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深入研究B树索引(五)
举报原因:
原因补充:

(最多只允许输入30个字)