| 小议index rebuild online | DBA之路：OoNiceDream |

小议index rebuild online

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/06/index_rebuild_online.html

一、前一篇文章的案例中提到,索引损坏了，重建索引时，直接rebuild报错，而rebuild online则可以，这主要是两者重建索引时的扫描方式不同，rebuild用的是“INDEX FAST FULL SCAN”，rebuild online用的是“TABLE ACCESS FULL”：

SQL> explain plan for
  2  alter index ind_test_id rebuild;
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 187312216
 
--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |             |   115K|   565K|    78   (2)| 00:00:10 |
|   1 |  INDEX BUILD NON UNIQUE| IND_TEST_ID |       |       |            |          |
|   2 |   SORT CREATE INDEX    |             |   115K|   565K|            |          |
|   3 |    INDEX FAST FULL SCAN| IND_TEST_ID |       |       |            |          |
--------------------------------------------------------------------------------------
 
10 rows selected.
 
SQL> explain plan for
  2  alter index ind_test_id rebuild online;
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3365522411
 
--------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |             |   115K|   565K|    78   (2)| 00:00:10 |
|   1 |  INDEX BUILD NON UNIQUE| IND_TEST_ID |       |       |            |          |
|   2 |   SORT CREATE INDEX    |             |   115K|   565K|            |          |
|   3 |    TABLE ACCESS FULL   | TEST        |   115K|   565K|    78   (2)| 00:00:10 |
--------------------------------------------------------------------------------------
 
10 rows selected.

二、rebuild index online在执行期间不会阻塞DML操作，但在开始和结束阶段，需要请求模式为4的TM锁。因此，如果在rebuild index online开始前或结束时，有其它长时间的事物在运行，很有可能就造成大量的锁等待。具体可以参考：
rebuild index online的锁机制浅析
11G中有所不同：
rebuild index online的锁机制浅析(续)

这里可以通过设置10626事件，避免阻塞该表上的其它DML操作,但在rebuild index online开始、结束阶段有其它事物未完成，则会失败，报ORA-00051: timeout occurred while waiting for a resource 错误：
（以下测试在9208、10203中测试通过，在10201中等待几秒后，直接报ORA-00051，之后再想重建则报ORA-08104，处理方法见后面，因而设置该事件还需谨慎）
会话一：

SQL> select max(sid) from v$mystat; 
 
  MAX(SID)
----------
        82
 
SQL> delete from test_ls where id=1;
 
1 row deleted.

会话二：

SQL> select max(sid) from v$mystat;
 
  MAX(SID)
----------
        70
 
SQL> alter session set events '10626 trace name context forever';
 
Session altered.
 
SQL> alter index IND_TEST_ID rebuild online;

此时会话二会话被阻塞。

会话三：

SQL> delete from test_ls where id=3;
 
1 row deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test_ls where id=3;
 
no rows selected

会话三的DML操作并不会被阻塞。

会话一rollback后，会话二报错：

alter index IND_TEST_ID rebuild online
*
ERROR at line 1:
ORA-00051: timeout occurred while waiting for a resource

增加表的数据量，同样可以测出在rebuild online结束时，如果该表上还有其它事物未完成，则报错，如果无其它事物，索引可以重建成功，期间都不会阻塞其它DML操作。

三、rebuild onlie时，如果发生意外中断，很容易造成ORA-08104错误，之后再想rebuild、drop索引都会报错。10G之前需要等待SMON去清理，10G以后可以使用DBMS_REPAIR.ONLINE_INDEX_CLEAN进行手工清理：

SQL> alter index IND_TEST_ID rebuild online;
alter index IND_TEST_ID rebuild online
*
ERROR at line 1:
ORA-08104: this index object 93996 is being online built or rebuilt
 
SQL> DECLARE 
  2    RetVal BOOLEAN;
  3    OBJECT_ID BINARY_INTEGER;
  4    WAIT_FOR_LOCK BINARY_INTEGER;
  5  
  6  BEGIN 
  7    OBJECT_ID := 93996;
  8    WAIT_FOR_LOCK := NULL;
  9  
 10    RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN ();
 11    COMMIT; 
 12  END; 
 13  /  
 
PL/SQL procedure successfully completed.
 
SQL> alter index ind_test_id rebuild online;
 
Index altered.