sql server中index的REBUILD和REORGANIZE

这是曾经发生的一次故障，作为教训和备忘还是决定记录下来。

生产系统有个千万行级别的表，原本是晚上跑的job中，因某个存储过程中的批量插入语句直到第二天白天没法执行完原因，合作伙伴工程师觉得系统有点慢，为了加开插入速度而直接将该表主键意外的索引都删除掉了。结果出现大量全表扫描和行锁竞争（row lock contention），导致OLTP系统根本无法办理业务。虽然作为DBA的我采用了rebuild online方式，但是还是无济于事，终于关掉应用后重新创建了索引，具体所花时间如下：

2010-9-4 系统崩溃事件

1、删除表table_name上除主键意外的索引 10分钟

2、执行存储过程(含批量插入语句)

3、重建索引 1.5小时

4、表和索引统计信息重新收集 10分钟

 

以上过程中只需要用到如下简单SQL语句:

drop index index_name

Alter indexindex_name rebuild;

Alter indexindex_name rebuild online;

 

create index index_name on table_name (column_name)

tablespace tablespac_name

 

 

BEGIN

dbms_stats.gather_table_stats(ownname => 'Owner',tabname => 'table_name',cascade => TRUE);

end;

 

实际上作为DBA，对生产系统执行任何操作时，仅仅懂这些还是远远不够的。需要明确如下几点：

1、是不是业务时间（或业务高峰期），如果是，则尽量不要进行类此操作。因为在大对象中创建索引时不仅需要较大temp表空间，而且基于表原有索引的所有SQL语句的执行计划都发生变化。这样，这些SQL的重新解释需要大量CPU资源。

2、当原来索引被删除后，出现大量的全表扫描。这不仅对系统I/O产生压力，而且buffer catch中的已缓存数据块很容易被挤出去，不仅对SGA带来压力，而且对I/O产生恶性循环。

3、综合CPU、内存、I/O方面资源紧张，在加上正常业务办理需要的各类DML操作，做种导致row lock contention、read by other session等一堆等待，最终系统被出现无法办理业务的等待（系统慢）状态。

4、了解rebuild和rebuild online区别。

 

因此在进行重建或创建所以之前，很有必要充分考虑生产环境、数据库对象的大小和用途、temp表空间大小，硬件资源、回退方案等等

 

注:rebuild和rebuild online的区别

1、当rebuild 时一般对原先索引进行INDEX FAST FULL SCAN。

2、当rebuild online的时不用原先索引而执行TABLE ACCESS FULL

3、rebuild和rebuild online都会发生sort，即需要用到temp表空间。

4、rebuild 会阻塞dml语句而rebuild online则不会。

5、rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。