创建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;

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

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