演示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----------
