索引整理

几种常用的在线索引整理方式：
(1) alter index rebuild
(2) alter index rebuild online
(3) alter index coalesce
(4) alter index shrink space
(5) alter index shrink space compact
(6) alter index shrink space cascade

这里简单做一下说明：
alter index rebuild
扫描现有索引块进行索引重建，会下降索引高水位，一般执行速度快，但会全程对表加锁，阻塞DML操作。
Can use the existing index to create the new version. Can be optimized for reduced overheads.
Locks the table for the duration of the rebuild. "Doubles" space usage temporarily. May require massive sorts. Can "cause" Oracle error 01410.

alter index rebuild online
扫描全表进行索引重建，会下降索引高水位，一般执行速度慢，只在开始和结束时对表加锁，执行中间不阻塞DML操作。
Does not lock table for entire rebuild. Can be optimized for minimal overheads.
Locks table at start and end of rebuild. Cannot use the index to rebuild the index. "Doubles" space usage temporarily.
Adds row-level trigger to table actions. May require massive sorts. Can "cause" Oracle error 01410.

alter index coalesce
● 对索引块做合并操作，不会下降索引高水位，可随时中断。全程不阻塞DML操作。
● 操作后页块数量下降，而branch枝块和root根块的结构是不会变化的。
● 并不释放索引上的多余空间，但索引结构实际占用的空间BTREE_SPACE下降。
● 不会导致索引BLEVLE降级。
Completely "online" process as it doesn't do any table locking. Repacks within existing index structure.
Can generate a lot of redo. Not very aggressive about repacking so only useful for special cases (until 10g). Can 'cause' ORA-01555 errors

alter index shrink space
● 收缩索引并降低高水位。在结束时对表加锁，执行中间不阻塞DML操作。
● REDO空间消耗比COALESCE高。

alter index shrink space compact
收缩索引不降低高水位。全程不阻塞DML操作。

alter index shrink space cascade
收缩索引同时收缩表，一般不怎么使用。在结束时对表加锁，执行中间不阻塞DML操作。