教你如何成为Oracle 10g OCP - 第九章对象管理(8) - 如何重建B树索引

最新推荐文章于 2020-12-21 11:16:18 发布

cuixie2370

最新推荐文章于 2020-12-21 11:16:18 发布

阅读量90

点赞数

9.2.3 如何重建B树索引

关于索引的一些说明：

1.索引的存储是：块内无序，块间有序；
2.rebuild index时对于索引块的读取也是Index Fast full scan
3.不是说读索引的代价高，而是顺序读取块内无序的索引的代价高
所以会采用Fast Full Scan方式读取

------------------------------------------------------
rebuild index的详细分析见：
http://space.itpub.net/35489/viewspace-594278

alter index xx rebuild online ; 会走 Full Table Scan .
alter index xx rebuild ; 可能走Index Fast Full Scan，取原index数据
作为数据源，也有可能取全表扫描即表的数据作为数据源，取决于统计数据，
如果索引大小比表大，那么会选择使用全表扫描表中的数据。走Index Fast
Full Scan , 多块读，按照物理block存储的顺序读取而不是index tree创建
的逻辑顺序读取，之所以这样做，是因为Sequential multiblock reads是最
快的IO访问方式. I/O的快速带来的负面影响就是要重新排序 . rebuild index
源数据来源于原来的index还是table, 取决于统计信息，假如统计信息显示索
引比表还大，那么无疑走全表比走索引快，因此走全表扫描。所以统计信息的
完整与否影响到数据来源。

从索引开始rebuild online 的那一刻起,oracle会先创建一个SYS_JOURNAL_xxx
的系统临时日志表,结构类似于物化视图日志表mlog$_表, 通过内部触发器,
记录了开始rebuild索引时表上所发生的改变的记录,当索引已经创建好之后,
新数据将直接写入索引,只需要把SYS_JOURNAL_xxx日志表中的改变维护到索
引中即可。在rebulid index online 的时候走的是full table scan，这
时候需要排序；在rebulid index 走的index ffs (fast full scan)，而ffs
搜索的顺序是根据 leaf block 的物理存储顺序相关

rebuild index加 online时：存在DDL锁，但是没有DML锁
rebuild index不加 online , DDL锁和DML锁都有

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

当进行index full scan的时候 oracle定位到索引的root block，然后
到branch block（如果有的话），再定位到第一个leaf block, 然后根据
leaf block的双向链表顺序读取。它所读取的块都是有顺序的，也是经过
排序的。

而index fast full scan则不同，它是从段头开始，读取包含位图块，root
block,所有的branch block, leaf block，读取的顺序完全有物理存储位置
决定，并采取多块读，没次读取db_file_multiblock_read_count个块。
index fast full scan 会比index full scan多一步sort order by

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

重建索引有两种方法：

一种是最简单的，删除原索引，然后重建；第二种是使用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操作。rebuild index online会是full table scan .

而同时，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 …validate structure命令以后检查index_stats视图pct_used
字段来判断是否有必要重建索引。

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

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

在图中，我们有一个表，该表有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';

那么当我们将表里的数据按照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。
但是如果某个表存在多个索引的时候，需要仔细决定应该选择哪一个索引列来重
建表。

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

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