oracle 表结合 索引,Oracle 大表建立索引

Oracle 大表建立索引

祖仙教小凡仙 海鲨数据库架构师 数据库

有个2亿记录的表，发现须要添加一个联合索引，结果就采用普通的create index index_name on tablename (entp_id,sell_date),结果悲剧了，把全部的DML语句都阻塞了，致使系统不能正常使用，还好是晚上10点，用户不是很是多，1个小时候，索引结束，阻塞解决；上网查了一下，若是加上 online参数后，就能够在线作索引，而不须要阻塞全部的DML语句，血的教训，拿出来与各位共勉，架构

建立测试表ide

* 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 indexcode

会话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：it

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：table

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：class

--建立索引(由于要在 会话二、会话3 中作其它操做，因此表中数据要量要足够大)

10:25:08 SQL> create index ix_test_col1 on t_test(col1);test

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 回滚段的味道