bitmap的一些block影响

lunar@TSMISC02> create table t(id number, bitmap_flag varchar2(5));

Table created.

Elapsed: 00:00:00.01
lunar@TSMISC02> create bitmap index t_idx on t(bitmap_flag);

Index created.

Elapsed: 00:00:00.01
lunar@TSMISC02> select distinct sid from v$mystat;

       SID
----------
        16

Elapsed: 00:00:00.00
lunar@TSMISC02> insert into t values(1,'Y');

1 row created.

Elapsed: 00:00:00.00
lunar@TSMISC02>

 

另开一个窗口（session 18）：
lunar@TSMISC02> select distinct sid from v$mystat;

       SID
----------
        18

Elapsed: 00:00:00.00
lunar@TSMISC02> insert into t values(2,'Y');
。。。。。。
hang。。。
。。。。。。

回到session 16:
lunar@TSMISC02> select * from v$lock where block=1;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
56AF9100 56AF920C         16 TX     917516       2650          6          0        107          1

Elapsed: 00:00:00.10
lunar@TSMISC02> select * from v$lock where id1=917516;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
56AF9100 56AF920C         16 TX     917516       2650          6          0        131          1
56395E38 56395E48         18 TX     917516       2650          0          4         99          0

Elapsed: 00:00:00.01
lunar@TSMISC02>

可见，session 16 阻塞了 session 18.

在session16 提交刚才的insert操作，再观察：
lunar@TSMISC02> commit;

Commit complete.

Elapsed: 00:00:00.00
lunar@TSMISC02> select * from v$lock where block=1;

no rows selected

Elapsed: 00:00:00.01
lunar@TSMISC02> select * from t;

        ID BITMA
---------- -----
         1 Y

Elapsed: 00:00:00.00
lunar@TSMISC02>

 

再回到session 18 观察：
lunar@TSMISC02> insert into t values(2,'Y');

1 row created.

Elapsed: 00:02:43.49
lunar@TSMISC02> select * from t;

        ID BITMA
---------- -----
         1 Y
         2 Y

Elapsed: 00:00:00.00
lunar@TSMISC02>

可见阻塞已经消除了。