重建索引有多種方式，如drop and re-create、rebuild、rebuild online等。下面簡單比較這幾種方式異同以及優缺點：

三、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的資料來源是基表。

oracle重建索引（三）

相關文章