index rebuild online

SQL> select segment_name ,segment_type from user_segments where TABLESPACE_NAME='INDEX_TS';

SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE
------------------
EMP_IDX
INDEX

EMP_PK
INDEX

DEPT_PK
INDEX

SQL> select * from emp where empno='123';
select * from emp where empno='123'
*
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/oradata/index_ts01.ora'

SQL> create tablespace index_ts_temp datafile '/u01/oradata/index_ts_temp01.ora' size 128m;
connect
Tablespace created.

SQL> drop tablespace index_ts INCLUDING CONTENTS;
drop tablespace index_ts INCLUDING CONTENTS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter index DEPT_PK rebuild tablespace index_ts_temp;
alter index DEPT_PK rebuild tablespace index_ts_temp
*
ERROR at line 1:
ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/oradata/index_ts01.ora'

 

因为不是在线rebuild ，所以会读取原来index 的数据

SQL> alter index DEPT_PK rebuild tablespace index_ts_temp online;

Index altered.

加上online 表示不读取原来index 的数据，直接从table 里面读取数据重新建立 index

 

 

 

 

 

一、前一篇文章的案例中提到,索引损坏了，重建索引时，直接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.