ORACLE 为什么无需重建索引 索引碎片

碎片的产生： 

实操：

先建表，建索引

然后插入1000000条数据

分析索引：analyse index t_idx validate structure;

查看分析结果：select name,height,pct_used,del_lf_rows/lf_rows from index_stats;

然后我们来破坏一下索引，重新查看一下分析结果

可以看到最后一个指标变了。说明产生一些碎片了。那么需要进行整理：

可见最后一个指标正常了（低于0.2）。

Oracle数据库中的索引什么时候需要重建呢？或者什么情况下需要重建索引呢？Oracle需要定期重建索引吗？如果不需要重建索引，那么这样做的理由是什么？如果需要重建索引，那么这样做的理由又是什么？另外，如果需要重建索引，那么满足哪些条件的索引才需要重建呢？关于这个问题，网上也有很多争论，也一直让我有点困惑，因为总有点不得庐山真面目的感觉，直到看到了文档 ID 186826.1等这些资料。

首先来看看网上关于索引需要重建的原因或标准：

一：分析(analyze)指定索引之后，查询index_stats的height字段的值，如果这个值>=4 ,最好重建（rebuild）这个索引。虽然这个规则不是总是正确，但如果这个值一直都是不变的，则这个索引也就不需重建。

二：在分析(analyze)指定索引之后，查询index_stats的del_lf_rows和lf_rows的值,如果(del_lf_rows/lf_rows)*100 > = 20，则这个索引也需要重建。

关于这个论据，我们找到可以考证的官方资料为Various Aspects of Fragmentation (文档 ID 186826.1) ，其中有这么一段

Along with the REBUILD clause of the ALTER INDEX, Oracle8i introduces the

COALESCE clause as another way to address fragmentation issues.


In the following cases, it may be worthwhile to rebuild the index:
 
     --> the percentage of the space used is bad - lower than 66%: PCT_USED
 
     --> deleted leaf blocks represent more than 20% of total leaf blocks: DEL_LF_ROWS 
 
     --> the height of the tree is bigger than 3: HEIGHT or BLEVEL

但是这个资料非常古老了，是Oracle 8， Oracle 9i时代的资料，当然对于索引深度超过4级以及已删除的索引条目至少占有现有索引条目总数的20%，这2种情形下需要重建索引，这种说法显然也没有过时，但是关于PCT_USED低于66%的就建议重建索引，这种说法对现在主流版本有点不合时宜。

另外网上还有一些重建索引的理由或说法，如下所示：

1. Oracle的B树索引随着时间的推移变得不平衡(错误的认识)
2. 索引碎片在不断增加
3. 索引不断增加，删除的空间没有重复使用（错误的认识）
4. 索引 clustering factor （集群因子）不同步，可以通过重建修复(错误的认识)

关于这些知识点，其实ORACLE官方文档有关于索引重建的必要性和影响的讨论， 官方文档为索引重建的必要性与影响 (文档 ID 1525787.1)

适用于:

Oracle Database Cloud Schema Service - 版本 N/A 和更高版本
Oracle Database Exadata Express Cloud Service - 版本 N/A 和更高版本
Oracle Database Exadata Cloud Machine - 版本 N/A 和更高版本
Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本
Oracle Database Backup Service - 版本 N/A 和更高版本
本文档所含信息适用于所有平台

用途

本文章将重点概述重建索引的各种影响。我们通常会定期重建索引，但事实上，判断索引重建是否有用一般并不以统计数字为基础，而且很少保留索引的重建历史记录。

适用范围

本文章的目标受众是数据库管理员。

详细信息

关于重建索引有用与否的讨论有很多。一般而言，极少需要重建 B 树索引，基本原因是 B 树索引很大程度上可以自我管理或自我平衡。

认为需要重建索引的最常见理由有：

- 索引碎片在不断增加
- 索引不断增加，删除的空间没有重复使用
- 索引 clustering factor （群集因子）不同步

事实上，大多数索引都能保持平衡和完整，因为空闲的叶条目可以重复使用。插入/更新和删除操作确实会导致索引块周围的可用空间形成碎片，但是一般来说这些碎片都会被正确的重用。Clustering factor 群集因子可以反映给定的索引键值所对应的表中的数据排序情况。重建索引不会对群集因子产生影响，要改变集群因子只能通过重组表的数据。

另外，重建索引的影响非常明显，请仔细阅读以下说明：

1.大多数脚本都依赖 index_stats 动态表。此表使用以下命令填充：

analyze index ... validate structure;

尽管这是一种有效的索引检查方法，但是它在分析索引时会获取独占表锁。特别对于大型索引，它的影响会是巨大的，因为在此期间不允许对表执行 DML 操作。虽然该方法可以在不锁表的情况下在线运行，但是可能要消耗额外的时间。

2.重建索引的直接结果是 REDO 活动可能会增加，总体的系统性能可能会受到影响。

插入/更新/删除操作会导致索引随着索引的分割和增长不断发展。重建索引后，它将连接的更为紧凑；但是，随着对表不断执行 DML 操作，必须再次分割索引，直到索引达到平衡为止。结果，重做活动增加，且索引分割更有可能对性能产生直接影响，因为我们需要将更多的 I/O、CPU 等用于索引重建。经过一段时间后，索引可能会再次遇到“问题”，因此可能会再被标记为重建，从而陷入恶性循环。因此，通常最好是让索引处于自然平衡和（或）至少要防止定期重建索引。

3.通常是优先考虑index coalesce（索引合并），而不是重建索引。索引合并有如下优点：

- 不需要占用近磁盘存储空间 2 倍的空间
- 可以在线操作
- 无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大，请见第 2 点中的解释。

注意：例如，如要将索引转移到其他表空间，则需要重建索引。

综上所述，强烈建议不要定期重建索引，而应使用合适的诊断工具。请参阅以下文章，其中列出了可用于分析索引结构的脚本。它不使用“analyze index validate structure” 命令，但将基于当前表和索引统计信息来估计索引大小。

Note 989186.1 - Script to investigate a b-tree index structure

看了上面官方文档，想必你已经对索引是否需要重建有了一个认识,但是，你是否总觉得这是一个结论性的说法，总觉得不够深入，缺乏论据支持呢，那么Richard Foote的这篇文档（index-internals-rebuilding-the-truth），绝对是我见过最详细、最深入介绍索引内部的知识。有兴趣可以去网上搜索这篇文档深入研究一下！

 

识别索引是否有碎片

 

获得关于索引的信息，用下面的命令

analyze index index_name validate structure 或validate index index_name

 

analyze index index1 validate structure：是用来分析索引的数据块是否有坏块，以及根据分析得到的
数据（存放在index_stats）來判断索引是否需要重新建立。

运行命令后，然后在视图 index_stats查询，这个视图记录索引的一些信息，这个视图只有一个记录，所以在同一时间
只能分析一个索引。

 

1.删除的行数如占总的行数的30%，即del_lf_rows / lf_rows > 0.3，那就考虑索引碎片整理
2.如果”hight“大于4，可以考虑碎片整理
3.如果索引的行数（LF_rows）远远小于‘LF_BLKS’ ，那就说明有了一个大的删除动作，需要整理碎片

 

索引碎片整理方法

 

1. recreate index
2. alter index skate.idx_test rebuild nologging parallel 4 online ;
3. 如果叶块是half-empty or near empty，可以运行“alter index coalesce”来合并
 

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

构建“高”索引

---

索引的高度(Btree Level)是影响索引的性能的因素之一，高度的增加，意味着对索引的读的次数的增加。如果索引高度要增加，其必要条件就是左枝或右枝饱和。

　　枝节点中左节点是个特殊的节点，它是枝节点数据块中唯一小于该枝节点键值的节点，其指针并不在枝节点的数据区，而是在枝节点数据块的头部，并且它没有记录所指向节点的键值，而只有一个指针，并且每个枝节点都必须有一个左节点。对于右节点，因为索引长度是受限制的(和数据块大小相关)，保证了一个节点中最少能放入一个键值。这2个特性决定了一个枝节点最少可以指向2个下一层节点。因此，即使索引键值数据足够大，要构建一颗高度为N的树也需要2^(n-1)条数据，最终的索引节点块为2^n-1块。例如，在一个块大小为2K的表空间中构建一个24层的索引，将需要32G的索引空间。

　　然而，在9i中，B树的左增长在算法上有缺陷：分裂时，如果枝节点已经饱和，则枝节点立即分裂。这样的话，在左分裂时，如果左枝上的所有枝节点已经全部饱和，即使右枝节点没有任何键值指针，也会造成左枝的“多米诺”式的分裂，造成树的高度增加(因为每个枝节点必须有一个左节点，因此右分裂不会造成这种情况)。利用这一缺陷，Jonathan Lewis只用24条数据就构建了一个24层高度的B树。

　　在10g中，这一缺陷已经被修正了：左枝节点饱和时，会先看其同父节点的右枝节点上是否有索引键数据(即是否存在指向右边的指针)，如果没有，则将左枝节点上的数据转移到右枝节点，从而释放左枝节点的空间。

　　那是不是在10g中，如果需要构建一颗“高”数，就必须要如此大的空间呢?实际上，我们还可以利用右分裂的一个规则生成一个size很小但层数很高的索引：右分裂时，只要右枝节点是饱和就会分裂，而没有考虑对应的左枝节点上是否存在键值数据。利用这一规则，我们在构造出右枝的第一节点后，就将左枝上的右边数据删除，从而保证索引只占用少量数据。不过这一方法还是需要大量的中间数据。以下代码中，实际生成的表(HWM)、索引不足1M。

　　还有一点就是，节点数据块为空后，会被放到freelist的头部去，当索引分裂需要新数据块时，会先从freelist的头部先取出数据块进行分配，因此这就是我们以下代码能始终保证索引的size很小的原因。

　　注意：下面代码不要在生产环境上运行，运行前建议关闭Archive模式。

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->13:51:02 HELLODBA.COM> conn demo/demo

　　Connected.

　　13:51:08 HELLODBA.COM> create table idx_split (a number, b varchar2(1446), c date) nologging pctfree 0;

　　Table created.

　　13:51:20 HELLODBA.COM> create index idx_split_idx on idx_split (b, a) tablespace idx_2k nologging pctfree 10;

　　Index created.

　　13:51:20 HELLODBA.COM> select object_id from user_objects where object_name='IDX_SPLIT_IDX';

　　OBJECT_ID

　　----------

　　126371

　　13:52:00 HELLODBA.COM> conn demo/demo

　　Connected.

　　13:52:21 HELLODBA.COM> set serveroutput on

　　13:52:21 HELLODBA.COM> set time on

　　13:52:21 HELLODBA.COM> truncate table idx_split;

　　Table truncated.

　　

declare
v_level number;
v_start number;
procedure build_tree( p_bl in number, p_rt in number, p_lst in number)
as
pragma autonomous_transaction;
v_rt number;
v_lst number;
begin
v_lst := p_lst;
v_rt := p_rt;
if (p_bl<=1) then
if v_rt > v_lst then
insert /*+append*/ into idx_split (a, b, c) values (v_rt, lpad('0',1446,'0'), sysdate);
 --dbms_output.put_line('insert - '||(v_rt));
v_lst := v_rt;
commit;
end if;
return;
end if;
-- build the left tree
build_tree(p_bl-1, v_rt, v_lst);
v_rt := v_rt + power(2, p_bl-1)-power(2, p_bl-2);
if (p_bl > 3) then
if (v_rt > v_lst) then
insert /*+append*/ into idx_split (a, b, c) values (v_rt, lpad('0',1446,'0'), sysdate);
--dbms_output.put_line('insert - '||(v_rt));
 v_lst := v_rt;
 v_rt := v_rt + 1;
commit;
end if;
 delete from idx_split where a between p_rt+1 and p_rt+power(2, p_bl-2)-1;
 --dbms_output.put_line('delete - '||(p_rt+1)||' ~ '||(p_rt+power(2, p_bl-2)-1));
 commit;
end if;
-- build the right tree
build_tree(p_bl-1, p_rt + power(2, p_bl-1)-power(2, p_bl-2), v_lst);
--execute immediate 'alter index idx_split_idx shrink space compact';
 dbms_output.put_line('level - '||p_bl);
 end;
begin
v_level := 24;
 v_start := 1;
build_tree(v_level, 1, 0);
insert /*+append*/ into idx_split (a, b, c) values (power(2, v_level-1) + 1, lpad('0',1446,'0'), sysdate);
delete from idx_split where a between 2 and power(2, v_level-1);
dbms_output.put_line('deleted - '||SQL%ROWCOUNT||' rows.');
commit;
insert /*+append*/ into idx_split (a, b, c) values (power(2, v_level-1) + 2, lpad('0',1446,'0'), sysdate);
end;

　　达到索引高度限制(24)后 ，以上代码抛出了600错误。　

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->ORA-00600: internal error code, arguments: [6051], [], [], [], [], [], [], []
　　以上代码在PC server上运行了5个小时，表和索引的高水位都不足1M：　

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->HELLODBA.COM> select segment_name, bytes/1024/1024 "size(M)" from user_segments where segment_name like 'IDX_SPLIT%';

　　SEGMENT_NAME size(M)

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

　　IDX_SPLIT .125

　　IDX_SPLIT_IDX .75

　　HELLODBA.COM> validate index IDX_SPLIT_IDX;

　　Index analyzed.

　　HELLODBA.COM> select NAME, HEIGHT, BLOCKS, BR_BLKS, LF_BLKS from index_stats;

　　NAME HEIGHT BLOCKS BR_BLKS LF_BLKS

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

　　IDX_SPLIT_IDX 24 384 294 27
　　值得注意的是，在OLTP系统中，因为会对表进行大量的增、删、改，难免会出现上述情况——右(左)增长时，左(右)枝存在很大空闲，造成这样的现象：数据量很少，但索引树的高度却很高。

----------------------------------------------5-5 分裂和9-1分裂

如何分裂

当一个事务需要修改（大多数情况是Insert操作，某些情况下也可能为 Delete 操作）索引块（枝节点或叶子节点）上的数据，但没有足够空间容纳新的数据（包括索引条目、ITL slot）时，会将原有块上的部分数据放到一个新的数据块上去，这一过程就是索引块分裂（Index Block Splitting）。

按照分裂的对象不同，分为叶子节点分裂和枝节点分裂，而枝节点分裂中还有一个特殊的分裂：根节点分裂。

按照分裂时，2个数据块上分布的数据比例，分为5-5分裂和9-1分裂：

§5-5分裂：新旧2个数据块上的数据基本相等；

§9-1分裂：大部分数据还在原有数据块上，只有少量数据被转移到新的数据块上。

1)叶子节点分裂

当 Insert、Update（实际上就是 Delete+Insert）时，叶子节点块上没有足够空间容纳新的索引条目，就会发生叶子节点分裂：

在10224事件的 trace 文件中可以看到叶子节点块分裂的记录：

同时，将 Btree 结构 dump 出来，也可以看到节点被分裂：

2、当事务需要修改节点上的数据，叶子节点上没有足够空间容纳新的 ITL slot 时，也会发生分裂。

我们 dump 出一个“满”的节点，注意到它上面的空闲空间只有20字节，小于一条 ITL slot的大小（24字节）

并且此时它里面有一条空闲 ITL slot（第一条ITL slot是用于递归事务的，后面会有解释），先用一个事务占用它：

然后再启动一个事务，造成了空间不足分配新的 ITL slot，而导致节点分裂：

在10224 trace文件中记录此次分裂：

枝节点分裂

枝节点的下一层的节点分裂，会导致在枝节点上增加一条记录指向新增加的节点，当此时枝节点上空间不足时，会导致枝节点分裂。
这种情况很容易被重现，我们这就不放 demo 代码了，下面是 trace 文件中记录的枝节点分裂：

要注意的是，枝节点中存储的数据是比较特殊的，因而数据的分布会直接影响到枝节点的多少以及其分裂的频率。

在枝节点中，每条记录指向了下一层一个节点上的最小值，但其并不一定完整的存储了索引字段上的数据：

对于单个字段，如果字段的前面一部分数据就可以定位到下一层的节点块，则枝节点中只存储这一部分数据；例如，字段A的索引节点的第一个叶子节点上的字段数据是 AAA11111, AAA22222, …. AAA55555；第二个节点上的字段数据是 AAA66666,….AAA99999，那么，在枝节点上分别存储的数据是 AAA1 和 AAA6

对于复合字段索引，如果前面字段已经可以定位到下一层的节点块，则枝节点中只存储这些字段，而不存储后面的字段值。例如，在字段(A, B)上建立了索引，A的值是自增长的，所以通过A就可以定位到下一层的节点，在枝节点上就只存储了A的数据:

我们将一个枝节点 dump 出来，可以看到B字段的数据没有被记录：

正因为枝节点的这种的索引值的存储方式，在下面例子中，字段在索引中的顺序不同直接导致了索引的高度不同：

可以看到，idx_split_idx1和idx_split_idx2 中的字段是一样的，因此它们的叶子节点数也是一样的，但是因为它们的数据分布性不同以及在索引中的位置不相同，导致它们的枝节点的数量和索引高度有很大的差别。同时，通过 10224 事件的 trace 文件也可以看到，发生在 idx_split_idx2 上的枝节点分裂次数远远多余在 idx_split_idx1上发生的次数。

根节点分裂——特殊的枝节点分裂

在所有枝节点中，有一个特殊的枝节点（或许你可以将它作为一种单独的节点类别），那就是根节点。根节点上的数据已经导致根节点分裂的条件基本上和普通枝节点相同，但是，唯一不同的是，普通枝节点或叶子节点在分裂时，只分配一个新的数据块，然后将被分裂的数据块上的部分数据转移到新的数据块上去，而根节点的分裂是需要分配2个新的数据块，将原有数据分别转移到2个新的数据块上去，在原有节点上生成2条记录分别指向这2个新的数据块。下面的Trace记录的就是根节点的分裂，可以看到它获取了2个新的数据块：-----增加一层

9-1分裂

当事务向索引中最后一个叶子节点数据块上插入一条大于或等于（ROWID 大于最大值的ROWID）数据块上最大值的数据，且该数据块上不存在其它未提交事务时，此时如果没有足够空间，就会发生9-1分裂：即分裂时，绝大部分数据保留在原有节点数据块上，仅少量数据被转移到新数据块上。

注意：当向索引中插入大于、等于最大值的数据时，PCTFREE 会被忽略（我们在后面会介绍索引中 PCTFREE 和 INITRANS 的影响）

注意2：如果叶子节点分裂导致枝节点也分裂，枝节点的分裂比例和叶子节点的分裂比例是相同的。

下面例子中，枝节点和叶子节点都发生了9-1分裂：

注意，这里的统计结果中，枝节点的分裂方式并未显示，但从 Trace 文件中可以看到，新分裂的节点数据块上只有少量数据，发生的是9-1分裂：

5-5分裂

有3种情况会导致5-5分裂：

当新插入的数据小于索引中的最大值时，此时数据块空间不足容纳新的键值；

当插入、删除数据时，数据块上没有足够空间分配新的ITL slot；

当新插入的数据大于或等于索引中最大值时，此时数据块上还存在其它未提交的事务。

第一种情况很常见，这里不举例了。第二种情况可以参见之前的例子。下面代码是第三种情况的例子代码：

可以看到该分裂为5-5分裂，从索引树结构上也可以看出：

实际上，无论是9-1分裂还是5-5分裂，其目的都是为了减少分裂，因为节点分裂是一个代价高昂的操作：

当发生9-1分裂时，通常是索引的键值是递增的，且表上的主要操作为插入操作、事务并发量比较低的情况。保证新的数据块上有最大的空闲空间插入新值，因而减少了分裂的发生；

发生5-5分裂时，通常表上的并发事务较多，且插入、删除的数据比较分散，因此需要保持分裂的新、老数据块上有相当的空闲空间以容纳新事务、新数据。

-----------------------------------------ssd 时代 碎片还有意义嘛

抛开所有不相关的技术因素不谈；数据库上下文中的碎片是以无序方式存储的有序数据。这会导致不必要的性能开销，从而导致数据库速度减慢。

假设你有一张有员工的桌子。该索引包含按标识号访问员工的排序数据。索引包含按顺序存储的数据。为简单起见，我们有一个包含四名员工的表：

1   Anne
3   Charly
4   James
5   William

复制

数据库引擎在一个页面中存储几个雇员。这通常是一个固定大小的排序存储桶。因此，让我们将员工放在一个页面中。让我们假设我们只能在一个页面中放置两个员工。我们最终会得到：

[ Page 1, next page is page 2, there is no previous page ]
    1   Anne
    2   Charly

[ Page 2, there is no next page, but there is a previous page 1 ]
    4   James
    5   William

复制

现在，当我们想要添加识别号为3的Bert时，问题就出现了，这两个页面都放不下。我们需要为Bert创建一个新的页面，并修复(上一个和下一个)页面的引用，以便它们仍然是有序的。

[ Page 1, next page is page 3, there is no previous page ]
    1   Anne
    2   Charly

[ Page 2, there is no next page, but there is a previous page 3 ]
    4   James
    5   William

[ Page 3, next page is page 2, previous page 1 ]
    3   Bert

复制

请注意，第3页位于列表的末尾。数据库引擎仍然可以从第1页开始，按顺序遍历这些页；即先转到下一页3，然后再转到下一页2。然而，这并不是最优的。引擎需要来回跳转才能找到它的数据，而不是仅仅从第一页走到最后一页。这正是索引碎片的含义。

我们可以通过重新排序(和重建)页面来对索引进行碎片整理。我将省去具体的步骤，但这样做的结果是页面是有序的，其中包含的数据也是有序的。

[ Page 1, next page is page 2, there is no previous page ]
    1   Anne
    2   Charly

[ Page 2, next page is page 3, previous page 1  ]
    3   Bert
    4   James

[ Page 3, there is no next page, previous page 2 ]
    5   William

复制

你可能会问，为什么不马上做呢？总是有权衡的。更改最少的数据量(在本例中为页面)会影响最少的其他用户(在本例中为对数据库的其他查询或更改)。在将新页面放在最后的场景中，我们只需要更改几个页面。如果我们要将索引更新为完全有序，将需要更改大多数页面，如果不是全部页面的话。在更改页面时，对同一页面的其他更改(或更糟)必须等待前一个更改提交。