oracle 新建索引太久,在Oracle中，索引是否必须定期重建？索引重建有哪些影响？...

♣答案部分

一般而言，极少需要重建B树索引，基本原因是B树索引很大程度上可以自我管理或自我平衡。认为需要重建索引的最常见理由有：

lB-Tree索引随着时间的推移变得不平衡(错误的认识)；

l索引碎片在不断增加，但是这些碎片会被重用；

l索引不断增加，删除的空间没有重复使用(错误的认识)；

l索引聚簇因子(Clustering Factor)不同步，可以通过重建修复(错误的认识)。

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

若是重建索引，则建议对以下的索引进行重建：

①在分析(ANALYZE)指定索引之后，查询INDEX_STATS的HEIGHT字段的值，如果HEIGHT>=4即索引深度超过3级，那么最好重建(REBUILD)这个索引，但是如果这个值一直保持不变，那么这个索引也就不需要重建。

②在分析(ANALYZE)指定索引之后，查询INDEX_STATS的DEL_LF_ROWS和LF_ROWS的值，如果(DEL_LF_ROWS/LF_ROWS)*100>=20即已删除的索引条目至少占有现有索引条目总数的20%，那么表示这个索引也需要重建。

重建索引的影响非常明显，主要有以下几点：

(1)大多数脚本都依赖INDEX_STATS动态表，此表使用以下命令填充：1ANALYZE INDEX ... VALIDATE STRUCTURE;

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

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

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

(3)通常是优先考虑索引合并(INDEX COALESCE)，而不是重建索引。索引合并有如下优点：

l不需要占用过多的磁盘空间。

l可以在线操作。

l无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大。

如果将索引转移到其它表空间，那么需要重建索引。

综上所述，Oracle强烈建议不要定期重建索引，而应使用合适的诊断工具。为此，Oracle在Mos中给出了相关分析的脚本：“研究b-tree索引结构的脚本(文档ID 1577374.1)”。这个脚本将根据已存在的表和索引的统计信息来核实B-Tree索引结构，并可以估计索引的理论大小和索引布局，而且该脚本会将收集的信息以历史记录的形式保存在INDEX_HIST表中。这对避免做定时索引重建很有帮助。用户也可以自定义这个历史记录表。

该脚本的内容如下所示：1CREATE TABLE index_log (

2 owner          VARCHAR2(30),

3 index_name     VARCHAR2(30),

4 last_inspected DATE,

5 leaf_blocks    NUMBER,

6 target_size    NUMBER,

7 idx_layout     CLOB);

8

9ALTER TABLE index_log ADD CONSTRAINT pk_index_log PRIMARY KEY (owner,index_name);

10

11CREATE TABLE index_hist (

12 owner