转载

浅谈索引系列之索引重建

关于索引前面已经写了几篇博文，但写着写着发现依然有好多内容，不禁感叹Oracle的博大精深，就一个小小的概念，牵扯到的知识点以及内容何其多。本文就结合以前碰到的一个案例来谈一谈索引重建话题，如有错误，欢迎各位专家批评指正。
一：什么情况下要重建索引
索引是否需要定期重建一直是一个争论不休的话题。
部分DBA认为若基表频繁的增、删、改会造成大量索引碎片，重建后会改善索引性能。对于此观点翻阅了大量资料并没找到权威的说法，倒是在tom的《Export one on Oracle》找到了相反的观点：即认为索引会自我平衡和自我管理，即便基表经过频繁的增删改操作形成碎片，这些碎片在后续也可以被重用。

为证明tom的观点，在测试环境做了相关实验证明碎片确实可以重用，因此索引一般不会有碎片，基表频繁的增删改也不能成为重建索引的理由。那么究竟什么情况下要重建索引呢？
1.索引或索引分区因为介质故障损坏，例如索引所在表空间数据文件被误删除。
2.索引试图中STATUS列标记为UNUSABLE
3.索引对应基表move到新表空间
4.索引计划迁移到新表空间
5.修改索引的某些存储参数
二：重建索引的方法
1.重新创建
drop index index_name;
create index index_name;
2.alter index index_name rebuild;
3.alter index index_name rebuild online;
三：重建索引注意事项
此部分是本文的重点，将对上述的3中方法通过实验进行比较和说明，以及每种方法的利与弊。
为方便测试，创建了一整包含1000W左右的测试表，大小约为400M,创建完成后对表ID创建了索引，具体信息如下：

点击(此处)折叠或打开

desc t
Name Null? Type
-----------------------
ID NOT NULL NUMBER
DATA VARCHAR2(100)
SELECT count(*) FROM t;
COUNT(*)
----------
11030008
SELECT sum(BLOCKS)*8/1024 from user_extents t where t.segment_name='T';
SUM(BLOCKS)*8/1024
------------------
400
SELECT index_name,tablespace_name from user_indexes t where t.table_name='T';
INDEX_NAME TABLESPACE_NAME
------------------------------
IDX_T USERS

因后面的测试会观察表和索引所在的表空间使用情况，此处先粘贴一下表空间的情况

1.重新创建(drop-->create index)
SQL> drop index IDX_T;
  Elapsed: 00:00:00.41
SQL> create index idx_t on t(id);
  Index created.
Elapsed: 00:01:45.08
  共计用时146S，同时在create index语句执行过程中会话一直持有TM锁 Imode=4，阻塞了其他会话增删改等DML语句，直到索引创建完成。

2.alter index index_name rebuild;
先看看官方关于rebuild的说明：
浅谈索引系列之索引重建

官方很明确的说明了rebuild的原理，以原有索引作为基础进行重新创建（可以通过10046确认），且性能要好于drop然后重新create步骤，下面进行测试：
rebuild要持有相关锁，若表有未提交的事务，会报错，因此rebuild前请保证没有相关事务。
浅谈索引系列之索引重建

SQL> alter index idx_t rebuild;
Index altered.
Elapsed: 00:00:53.76

共计用时53S，相比上一种方法要快，像drop->create方法一样，rebuild语句执行过程会话一直持有TM锁Imode=4，阻塞其他会话增删改等DML语句，直到索引rebuild完成。相关锁截图如下：

rebuild还有一个值得关注的地方就是空间问题，Oracle在创建新索引过程中，并不会删除旧索引，直至新索引rebuild成功。那么说明
1)rebuild过程，若查询操作能用到旧索引依然会用旧索引，查询效率不会因rebuild而降低。以下是索引rebuild过程中语句执行的情况，可以清楚的看到执行计划用到了索引。
浅谈索引系列之索引重建

2)rebuild方式创建索引过程需要额外的空闲空间，额外空间的大小基本为旧索引大小，rebuild成功后，rebuild过程占用的额外空间将会被释放。以下是rebuild过程对表空间使用情况的一部分截图：

3.alter index index_name rebuild online;
同样看一下官方文档：
浅谈索引系列之索引重建

rebuild online执行过程中基表可以进行增删改等DML操作，在线rebuild可以保证了业务连续性，比rebuild要高级一些。
alter index idx_t rebuild online;
Index altered.
Elapsed: 00:00:58.92
共计用时58S，和第二种方法差不多。和rebuild一样，rebuild online索引重建过程也需要额外的空闲空间，另外rebuild online过程中旧索引依然可用，限于篇幅不再进行截图说明。
和rebuild不同的一点rebuild online以基表作为基础进行重新创建（可以通过10046确认），因此相对rebuild会慢一些。
rebuild online怎么保证重建过程不影响基表的DML操作呢？奥秘在于在rebuild online过程中，oracle会自动维护一日志(journal)表,通过锁以及dba_objects视图的相关信息可以跟踪到该表。

由于要额外维护一张日志表，那么如果在rebuild online过程中，基表发生了大量的增删改操作，整个rebuild online重建索引过程就会异常的慢。
另外还有一个特别重要的说明一下：虽然rebuild index online在执行期间不会阻塞DML操作，但在开始和结束阶段，需要请求模式为4的TM锁。如果在rebuild index online开始前或结束时，有其它长时间的事务在运行会阻塞
rebuild index online，通过以下实验说明：
会话一（35）：
浅谈索引系列之索引重建

会话二（55）：

通过TOM写的查询语句可以看到
浅谈索引系列之索引重建

在会话一中commit再次用上述语句检查发现已经没有阻塞了，等待一段时间rebuild online成功完成。

总结：
1.drop-->create index,alter index rebulid会阻塞基表的增删改DML操作，alter index rebuild online不会阻塞，保证在线重新创建。
2.alter index rebulid以及rebuild online重建过程要求有额外的空闲空间，drop-->create index无此要求。
3.一般情况下rebuild速度最快，针对rebuild online请保证重建开始前与结束前无相关事务，否则会rebuild online会一直处于等待状态。
上述三种重建索引方法各有利弊，在实际生产环境中具体采用哪种方法请结合具体情况酌情处理。
另外关于rebuild和rebuild online的区别，metalink上有一篇文章，请大家参考。
metalink Note:272762.1

Problem:
========
- Online Index rebuild takes a long time.
- ONLINE INDEX REBUILD SCANS THE BASE TABLE AND NOT THE INDEX

Symptoms:
=========
Performance issues while rebuilding very large indexes.
- The offline rebuilds of their index is relatively quick -finishes in 15 minutes.
- Issuing index rebuild ONLINE statement => finishes in about an hour.
- This behavior of ONLINE index rebuilds makes it a non-option for large tables
as it just takes too long to scan the table to rebuild the index. The
offline may not be feasible due to due to the 24/7 nature of the database.
- This may be a loss of functionality for such situations.
- If we attempt to simultaneously ONLINE rebuild the same indexes we may encounter
hanging behavior indefinitely (or more than 6 hours).

DIAGNOSTIC ANALYSIS:
--------------------
We can trace the sessions rebuilding the indexes with 10046 level 12.
Comparing the IO reads for the index-rebuild and the index-rebuild-online
reveals the following:

-ONLINE index rebuilds
It scans the base table and it doesn't scan the blocks of the index.

-OFFLINE index rebuilds
It scans the index for the build operation.

- This behaviour is across all versions.

Cause
Cause/Explanation
=============
When you rebuild index online,
- it will do a full tablescan on the base table.
- At the same time it will maintain a journal table for DML data, which has
changed during this index rebuilding operation.
So it should take longer time, specially if you do lots of DML on the same table,
while rebuilding index online.

On the other hand, while rebuilding the index without online option, Oracle will grab
the index in X-mode and rebuild a new index segment by selecting the data from
the old index. So here we are
- not allowing any DML on the table hence there is no journal table involved
- and it is doing an index scan
Hence it will be pretty fast.
Fix
Solution/Conclusion:
===========
- The ONLINE index rebuild reads the base table, and this is by design.
- Rebuilding index ONLINE is pretty slow.
- Rebuilding index offline is very fast, but it prevents any DML on the base table.

正文到此结束