alter index rebuild和rebuild online的區別

SQL> explain plan for
2 alter index test.IND_OBJECT_ID rebuild;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Note: cpu costing is off

11 rows selected.

SQL> explain plan for
2 alter index test.IND_OBJECT_ID rebuild online;

Explained.

SQL> select * from table(dbms_xplan.display);

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

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 6235 | 18705 | 10 |
| 1 | INDEX BUILD NON UNIQUE| IND_OBJECT_ID | | | |
| 2 | SORT CREATE INDEX | | 6235 | 18705 | |
| 3 | TABLE ACCESS FULL | T | 6235 | 18705 | 10 |
-------------------------------------------------------------------------

Note: cpu costing is off

11 rows selected.

SQL>

可見rebuild 和rebuild online的掃描方式不同，但都會發生sort.

在rebuild online 時會報錯

ORA-08120: Need to create SYS.IND_ONLINE$ table in order to (re)build index
Cause: Alter index Build/Rebuild online require existing of SYS.IND_ONLINE$ table.
Action: User/DBA needs to create sys.ind_online$ before alter the index /rdbms/admin/catcio.sql contains script to create ind_online$.

解決方法就是執行catcio.sql 建立SYS.IND_ONLINE$表

SQL> conn / as sysdba
Connected.
SQL> @D:oracleora92rdbmsadmincatcio

除了掃描方式不同外，rebuild 會阻塞dml語句而rebuild online則不會。

rebuild online時系統會產生一個SYS_JOURNAL_xxx的IOT型別的系統臨時日誌表，所有rebuild online時索引的變化都記錄在這個表中，當新的索引建立完成後，把這個表的記錄維護到新的索引中去，然後drop掉舊的索引，rebuild online就完成了。