rebuild index VS rebuild index online

create index和rebuild index时都会对原表加4号锁，对obj$加3号锁，阻止对原表的DML操作
create index online 和 rebuild index online 采用新增一张临时表来处理创建或者重建索引时对原表索引列的修改，这样就可以不用锁表，保证原表的dml操作在过程中可以正常执行。
rebuild的过程用户仍然可以走旧的索引来查询，索引重建索引需要原来索引两倍的空间

测试1：create表加锁
conn scott/tiger
select sid，username from v$session;
create index test_index on test(name); 

SID USERNAME
------- -------------
    139
    143 SCOTT
SQL> SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=143;
　　OBJECT_NAME LMODE
　　------------------------------ ----------
　　OBJ$ 3
　　TEST 4
测试2：rebuild online加锁
alter index test_index rebuild online;
SELECT OBJECT_NAME,LMODE FROM V$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE='TM' AND SID=143;
　　OBJECT_NAME LMODE
　　------------------------------ ----------
　　SYS_JOURNAL_10499 4
　　TEST 2

附锁类型
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */

测试3:在online重建过程中，数据库是如何记录的。

首先创建一张大表，保证有足够的时间观察索引重建过程的细节。
SQL> create table test_rebuild as select * from dba_objects;

Table created.

SQL> insert into test_rebuild select * from test_rebuild;

6398 rows created.
不停的插入数据

SQL> /

409472 rows created.

SQL> commit;

Commit complete.
SQL> desc test_rebuild
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> create index idx_rebuild on test_rebuild(object_name) ;

Index created.

接下来开始重头戏
开三个session
15:13:09 session1>alter index idx_rebuild rebuild online;
15:13:30 session2>select rowid,object_name from scott.test_rebuild where rownum<2;

ROWID              OBJECT_NAME
------------------ ------------------------------------------------------------
---------------------------
AAABoLAAFAAAAI0AAA old
15:13:41 session2> update scott.test_rebuild set object_name='new' where rownum<2;

1 row updated.

15:13:54 SQL> commit;

Commit complete.
15:13:57 session3> select * from scott.SYS_JOURNAL_6668;

C0 OPTODE PARTNO RID
-------------------------------------------------------
-------- - ---------- ------------------
old D          0 AAAAABAAFAAAAI0AAA
new I          0 AAAAABAAFAAAAI0AAA

当session1报Index altered.再查询select * from scott.SYS_JOURNAL_6668;就会提示表不存在

可见oracle在修改到索引列的过程中会把修改的值写入一张临时创建的表SYS_JOURNAL_xxxx,xxxx表示索引的object_id，可以通过查询select index_name,object_id from user_objects 得到
比如例子中update操作，在临时创建表中记录了两个动作，一个旧值D代表删除，一个新值I代表插入，同时记录了修改的rowid。修改到非索引列时SYS_JOURNAL_xxxx不会有记录。

补充：如果建的是组合索引create index idx_rebuild on test_rebuild(object_name,status) online
，则查询select * from scott.SYS_JOURNAL_6668;
C0                             C1      O     PARTNO RID
------------------------------ ------- - ---------- ------------------
new                            invalid I          0 BAEAAAAAFAAAAI0AAA
old                            invalid D          0 BAEAAAAAFAAAAI0AAA
建了两个列的组合索引，就会在临时创建表里有2个C开头的列