有个2亿记录的表，发现需要添加一个联合索引，结果就采用普通的create index index_name on tablename (entp_id,sell_date),结果悲剧了，把所有的DML语句都阻塞了，导致系统不能正常使用，还好是晚上10点，用户不是非常多，1个小时候，索引结束，阻塞解决；上网查了一下，如果加上 online参数后，就可以在线做索引，而不需要阻塞所有的DML语句，血的教训，拿出来与各位共勉，
创建测试表
create table t_test
(
col1 number,
col2 number
);
造测试数据（根据自己机器具体情况估计需要的数据量，使创建索引的时间大概在20-30秒）
insert into t_test
select rownum col1, rownum col2 from dual
connect by rownum<10000000;
commit;
create index
会话1：
SQL> set time on
10:22:01 SQL> set timing on
10:22:02 SQL>
--获取 会话1 sid
10:22:04 SQL> select sid from v$mystat where rownum=1;
SID
----------
144
Elapsed: 00:00:00.01
会话2：
SQL> set time on
10:22:06 SQL> set timing on
10:22:06 SQL>
--获取 会话2 sid
10:22:06 SQL> select sid from v$mystat where rownum=1;
SID
----------
147
Elapsed: 00:00:00.01
会话3：
SQL> set time on
10:22:11 SQL> set timing on
10:22:11 SQL>
--格式化输出
10:22:13 SQL> set line 200
10:23:03 SQL> col addr for a10
10:23:03 SQL> col kaddr for a10
10:23:03 SQL> col sid for 999999
10:23:03 SQL> col type for a10
10:23:03 SQL> col id1 for 99999999999
10:23:03 SQL> col id2 for 99999999999
10:23:03 SQL> col lmod for 99
10:23:03 SQL> col request for 99
10:23:03 SQL> col ctime for 999999
10:23:03 SQL> col block for 99
10:23:03 SQL> col table_name for a30
10:23:03 SQL>
会话1：
--创建索引（因为要在 会话2、会话3 中做其它操作，所以表中数据要量要足够大）
10:25:08 SQL> create index ix_test_col1 on t_test(col1);
Index created.
Elapsed: 00:00:59.73
会话2：
--修改指定行的索引字段，此时update语句会hang住，等待索引创建，从会话3 中的锁的情况可以看到 会话2 在等待 会话1
10:25:04 SQL> update t_test set col1=102400 where col2=102400;
1 row updated.
Elapsed: 00:01:02.63
会话3：
--查看此时锁的情况
10:24:29 SQL> select a.*, decode(a.type, \'TM\', b.object_name) table_name
from v$lock a, dba_objects b
where a.id1=b.object_id(+)
and a.sid in(144, 147);
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
---------- ---------- ------- ---------- ------------ ------------ ---------- ------- -----
144 TM 18 0 3 0 1 0 OBJ$
147 TM 5180637 0 0 3 0 0 T_TEST
144 TM 5180637 0 4 0 3 1 T_TEST
144 DL 5180637 0 3 0 3 0
144 DL 5180637 0 3 0 3 0
144 TX 655384 57423 6 0 3 0
6 rows selected.
Elapsed: 00:00:02.12
10:25:52 SQL> /
这里看到普通索引创建 要加TM TX DL 三把锁,更新语句正在等待要加TX锁
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
147 TM 5180637 0 3 0 4 0 T_TEST
147 TX 393221 56619 6 0 4 0
Elapsed: 00:00:00.77
当索引创建完 了后 更新语句才加到锁.
因此普通索引创建的时候 会阻塞 索引字段上的DML操作! 主要是因为不喜欢别的会话更改索引字段的值,否则导致索引叶节点的键值与表数据不一致.
create index online
会话1：
--删除索引，并加online选项重建
10:26:46 SQL> drop index ix_test_col1;
Index dropped.
Elapsed: 00:00:00.35
10:26:59 SQL> create index ix_test_col1 on t_test(col1) online;
Index created.
Elapsed: 00:02:47.07
会话2：
--修改指定行的索引字段，此时update不会待索引创建，而是很快结束
10:26:50 SQL> update t_test set col1=102400 where col2=102400;
1 row updated.
Elapsed: 00:00:09.21
会话3：
--查看锁的情况
10:26:53 SQL> /
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK TABLE_NAME
---------- ---------- ------- ---------- ------------ ------------ ---------- ------- ------- ----- ------------------------------
147 TM 5180637 0 3 0 6 0 T_TEST
144 TM 5180637 0 2 0 7 0 T_TEST
144 DL 5180637 0 3 0 8 0
144 DL 5180637 0 3 0 8 0
144 TM 5180671 0 4 0 7 0 144 TX 327692 57125 6 0 8 0
147 TX 655370 57432 6 0 6 0
7 rows selected.
Elapsed: 00:00:02.16
这里看到DML语句也加到了TX锁,而索引也加了3把锁.
.online创建索引时会临时创建一个IOT的表，把后期DML 记录到该表中,索引创建结束后删除IOT表 事实上就是类似于MVCC 回滚段的味道
