创建bitmap index 时对表的加锁方式

前一篇博客研究了 创建B*tree索引时 对表的加锁方式，现在来研究一下创建bitmap时对表的加锁方式。本文基于10gR2

测试表基于上一篇博客的test表。文档上面说9i不能以online方式创建bitmap index.

SQL> create table lock_obj (username varchar2(100),sid number,lock_type varchar2(100),
2 object_name varchar2(100),XIDUSN number,XIDSLOT number,XIDSQN number);

Table created

SQL> grant select any dictionary to robinson
2 ;

Grant succeeded

SQL> create or replace trigger t_create_index
2 before create on schema
3 begin
4 insert into lock_obj
5 select oracle_username username,session_id sid,decode(
6 locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',
7 5,'Share Row Exclusive',6,'Exlusive') lock_type,object_name,xidusn,xidslot,xidsqn
8 from v$locked_object,dba_objects
9 where v$locked_object.object_id=dba_objects.object_id;
10 end t_create_index;
11 /

Trigger created

SQL> select count(distinct owner) owner,count( distinct object_id) object_id,count(distinct object_name) object_name,count(distinct status) status,count(*) from test;

OWNER OBJECT_ID OBJECT_NAME STATUS COUNT(*)
---------- ---------- ----------- ---------- ----------
19 49791 29776 2 273857

SQL> create bitmap index b_status on test(status) online;

Index created

SQL> select * from lock_obj;

USERNAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- -------------------- ---------- ---------- ----------
ROBINSON 139 Row Exclusive LOCK_OBJ 0 0 0
ROBINSON 139 Row share TEST 0 0 0
ROBINSON 139 Row Exclusive OBJ$ 10 10 305
ROBINSON 139 Row Exclusive LOCK_OBJ 10 39 304
ROBINSON 139 Row Exclusive LOCK_OBJ 10 10 305
ROBINSON 139 Row share TEST 10 39 304
ROBINSON 139 Row Exclusive OBJ$ 10 10 305
ROBINSON 139 Row Exclusive LOCK_OBJ 10 39 304
ROBINSON 139 Row Exclusive LOCK_OBJ 10 10 305
ROBINSON 139 Share SYS_JOURNAL_51691 10 10 305
ROBINSON 139 Row share TEST 10 39 304

11 rows selected
SQL> delete from lock_obj;

11 rows deleted

SQL> commit;

Commit complete

SQL> drop index b_status;

Index dropped

SQL> create bitmap index b_status on test(status) ;

Index created

SQL> select * from lock_obj;

USERNAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- -------------------- ---------- ---------- ----------
ROBINSON 139 Row Exclusive LOCK_OBJ 0 0 0
ROBINSON 139 Share TEST 0 0 0

从实验中发现，创建B*tree index 和创建 bitmap index 时对表的加锁方式是一样的。

恩，对于rebuild 也应该是一样的 ，我比较懒 不做测试了。