rebulid index || rebuild index online || drop,creat index

　重建索引到底要不要排序？有人说要，因为创建索引时需要排序。有人说不要，因为重建索引的时候可以直接扫描旧的索引来重建成新的索引。让我们来看一下rebuild index到底需不需要排序。
　　
　　SQL> select name,statistic# from v$statname where name like '%sort%';
　　
　　NAME　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 STATISTIC#
　　---------------------------------------------------------------- ----------
　　sorts (memory)　　　　　　　　　　　　　　　　　　　　　　　　　 242
　　sorts (disk)　　　　　　　　　　　　　　　　　　　　　　　　　　 243
　　sorts (rows)　　　　　　　　　　　　　　　　　　　　　　　　　　 244
　　
　　看一下排序操作相关的 stat号
　　
　　再看一下rebuild index 的执行路径 SQL> explain plan for alter index ind_test_id rebuild;
　　
　　Explained.
　　
　　SQL> @?/rdbms/admin/utlxpls
　　
　　PLAN_TABLE_OUTPUT
　　-----------------------------------------------------------------------
　　| Id　| Operation　　　　　　　|　Name　　　　| Rows　| Bytes | Cost　|
　　-----------------------------------------------------------------------
　　|　 0 | ALTER INDEX STATEMENT　|　　　　　　　| 17837 | 71348 |　　11 |
　　|　 1 |　INDEX BUILD NON UNIQUE| IND_TEST_ID　|　　　 |　　　 |　　　 |
　　|　 2 |　 SORT CREATE INDEX　　|　　　　　　　| 17837 | 71348 |　　　 |
　　|　 3 |　　INDEX FAST FULL SCAN| IND_TEST_ID　| 17837 | 71348 |　　11 |
　　-----------------------------------------------------------------------
　　执行下rebuild 看看
　　
　　SQL> select STATISTIC#,value from v$mystat where STATISTIC# in(242,243,244);
　　STATISTIC#　　　VALUE
　　---------- ----------
　　　　　 242　　　　154
　　　　　 243　　　　　0
　　　　　 244　　　　242
　　
　　
　　SQL> alter index ind_test_id rebuild;
　　
　　Index altered.
　　
　　SQL> select STATISTIC#,value from v$mystat where STATISTIC# in(242,243,244);
　　STATISTIC#　　　VALUE
　　---------- ----------
　　　　　 242　　　　155
　　　　　 243　　　　　0
　　　　　 244　　　　242
　　可以看出sort(memory)增加了一次
　　
　　为什么要排序呢？因为rebuild index的时候走的index ffs，而ffs搜索的顺序是根据 leaf block 的物理存储顺序相关而跟键值的逻辑顺序无关（在"index full scan vs fast index full scan"这篇文章中有详细介绍）， 所以ffs的结果必须再做一次排序。
　　
　　此外在rebulid index online的时候走的是full table scan，这时候也是需要排序的，而且排序的次数会比较多， 在测试中发现每次做rebuild online产生4（10g中为13）次sort(memory),可能跟一些递规排序有关系。10g里面重建索引排序又有了一些改变，在10032事件的跟踪文件里面出现“Reopened sort”，目前暂时不知道是什么意思，希望有朋友能就这个问题进行探讨。

 

 

============================================================================================

 

 1、alter index ind1 rebuild ；
           这种方法是对已有的索引进行快速全扫描（如果当索引比表本身还大时也会full table scan的。）然后排序（为什么要排序？这时候Oracle是按照存储的顺序而不是index tree创建的顺序读取，而且索引在存储时是块间有序快内无序的。）再写回索引段，由其原理我们可以得知，这种方法在操作时是不允许对索引进行更改的也就意味着对应的表不能进行dml（查询不受影响，因原index依然可用）操作。从阻塞表上的操作来讲它和drop and create 是一样的，但其速度要快许多因为它扫描的是索引而不是表。当新索引建立后删除旧索引。
   2、alter index ind1 rebuild online;
         这种方法是对表数据进行全表扫描然后建立新的索引，在这个过程中old index 依然可用，因此它不会阻塞表上的dml操作，但其速度很慢。在建立新索引的过程中如果表上发生了dml操作，这些操作对索引的影响会记录到系统自动创建的JOURNAL TABLE AND INDEX内，等新索引建立完成后JOURNAL TABLE AND INDEX的内容会merge到新索引中，因此rebuild online 也应该在系统不太繁忙的时候进行，不然JOURNAL 表会很大，merge会很费时而merge会锁表对并发有影响。