oracle重建索引（三）

重建索引有多种方式，如drop and re-create、rebuild、rebuild online等。下面简单比较这几种方式异同以及优缺点：

相关文章：

oracle重建索引（一）

oracle重建索引（二）

三、rebuild和rebuild online的数据源

网上一直有这样一个说法：重建索引是以原索引作为数据源的。那么，这种说法是否准确呢？我们做实验来验证一下：

suk@ORACLE9I> COL SEGMENT_NAME FORMAT A30
--首先看看表和索引的大小
suk@ORACLE9I> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C1');

SEGMENT_NAME BYTES
------------------------------ ----------
TEST 201326592
IDX_TEST_C1 293601280

suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;

已解释。

suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
| 2 | SORT CREATE INDEX | | | | |
| 3 | TABLE ACCESS FULL | TEST | | | |
-----------------------------------------------------------------------

Note: rule based optimization

已选择11行。
--从执行计划可以看出，当索引比表大时，rebuild索引用的数据源是基表。

suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;

已解释。

suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
| 2 | SORT CREATE INDEX | | | | |
| 3 | TABLE ACCESS FULL | TEST | | | |
-----------------------------------------------------------------------

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当索引比表大时，rebuild online索引用的数据源是基表。

--我们为TEST添加一列，使得表比索引大
suk@ORACLE9I> ALTER TABLE TEST ADD(C2 CHAR(30) DEFAULT '1');

表已更改。

suk@ORACLE9I> SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME IN ('TEST','IDX_TEST_C
1');

SEGMENT_NAME BYTES
------------------------------ ----------
TEST 1476395008
IDX_TEST_C1 293601280

suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD;

已解释。

suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
| 2 | SORT CREATE INDEX | | | | |
| 3 | INDEX FAST FULL SCAN| IDX_TEST_C1 | | | |
-----------------------------------------------------------------------

Note: rule based optimization

已选择11行。

--从执行计划可以看出，当表比索引大时，执行计划已经改变，rebuild索引是以索引作为数据源的。

suk@ORACLE9I> EXPLAIN PLAN FOR ALTER INDEX IDX_TEST_C1 REBUILD ONLINE;

已解释。

suk@ORACLE9I> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | | | |
| 1 | INDEX BUILD NON UNIQUE| IDX_TEST_C1 | | | |
| 2 | SORT CREATE INDEX | | | | |
| 3 | TABLE ACCESS FULL | TEST | | | |
-----------------------------------------------------------------------

Note: rule based optimization

已选择11行。
--从执行计划可以看出，当表比索引大时，rebuild online仍然以基表作为数据源。

rebuild模式下，因为表数据不会产生变化，oracle主要考虑性能问题，把更快扫描完成的段作为数据源。在上面的例子中，我们并没有对表进行分析，故oracle应该根据数据段的大小来决定那个作为数据源的。一般索引字段比较多，或者对索引字段的DML操作较多，可能会导致索引比表大，这时oracle就会使用基表作为新索引的数据源进行rebuild了。
而在rebuild online模式下，因为允许DML操作，而表数据变化的同时索引也会跟着变化，为了索引与基表数据的一致性，比如采用基表数据作为数据源，而不能用原索引数据作为数据源。
我们用反证法证明不能用原索引作为新索引的数据源。
例如：
T1发出rebuild online命令
T2删除某条数据，删数据的同时，oracle会自动维护了旧索引
T3扫描经过T2数据所在索引节点
T4插入一条记录，新记录对应的索引节点刚好重用了T2删除的数据对应的索引节点空间

如果是这样的话，新建的索引将不包含T4插入的记录的信息。所以，rebuild online情况下新索引的数据源不能是原索引。

rebuild online情况下，如果非用原索引作为新索引的数据源的话，用中间表记录索引变化的方法应该是可以实现的，但由于数据变化会同时引起索引变化的特定决定了这种方法将异常复杂及效率底下，所以oracle不考虑旧索引作为新索引的数据源是有道理的。


结论：
1、rebuild会阻塞对基表的DML操作，但不会影响rebuild期间查询对原有索引的使用。
2、rebuild的数据源可能是基表，也可能是原索引。取决于基表和原索引的大小，那个小，rebuild时就会用那个作为数据源。这也说明了网上盛传的rebuild以原索引作为数据库的说法是不完全正确的。
3、rebuild online运行用户在索引重建期间执行DML操作。
4、rebuild online的数据源是基表。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63811/，如需转载，请注明出处，否则将追究法律责任。