演示DML操作导致位图索引锁定的简单示例

一、实验说明：

操作系统：rhel 5.4 x32

数据库：Oracle 11g R2

二、实验步骤：

2.1、在没有位图索引进行DML操作的例子(该实验建立b-tree索引做对比)：

 1 SQL> select distinct sid from v$mystat;
 2 
 3     SID
 4 ----------
 5      1
 6 
 7 SQL> create table jack_test (id int,name varchar2(10));
 8 
 9 Table created.
10 
11 SQL> insert into jack_test values(1,'aa');
12 
13 1 row created.
14 
15 SQL> insert into jack_test values(2,'bb');
16 
17 1 row created.
18 
19 SQL> insert into jack_test values(3,'cc');
20 
21 1 row created.
22 
23 SQL> insert into jack_test values(1,'dd');
24 
25 1 row created.
26 
27 SQL> commit;
28 
29 Commit complete.
30 
31 SQL> select * from jack_test;
32 
33     ID         NAME
34 ---------- ----------
35      1         aa
36      2         bb
37      3         cc
38      1         dd
39 
40 SQL> update jack_test set id=5 where name='aa';
41 
42 1 row updated.
43 
44 SQL> select distinct sid from v$mystat;
45 
46    SID
47 ----------
48     36
49 
50 SQL> update jack_test set id=100 where name='dd';
51 
52 1 row updated.

2.2、在有位图索引进行DML操作的例子：

 1 SQL> select distinct sid from v$mystat;
 2 
 3    SID
 4 ----------
 5     36
 6 SQL> create table jack_dml (id int,name varchar2(10));
 7 
 8 Table created.
 9 SQL> insert into jack_dml values(1,'aa');
10 
11 1 row created.
12 
13 SQL> insert into jack_dml values(2,'bb');
14 
15 1 row created.
16 
17 SQL> insert into jack_dml values(3,'cc');
18 
19 1 row created.
20 
21 SQL> insert into jack_dml values(1,'dd');
22 
23 1 row created.
24 
25 SQL> commit;
26 
27 Commit complete.
28 
29 SQL> select * from jack_dml；
30     ID        NAME
31 ---------- ----------
32      1         aa
33      2         bb
34      3         cc
35      1         dd
36 SQL> create bitmap index jack_dml_bmap on jack_dml(id);
37 
38 Index created.
39 
40 SQL> update jack_dml set id=4 where name='aa';
41 
42 1 row updated.
43 SQL> select distinct sid from v$mystat;
44 
45     SID
46 ----------
47      1
48 
53 SQL> update jack_dml set id=100 where name='dd';
54 -----waiting----------