oracle位图索引语句,oracle之位图索引

位图索引适用于低基数(low-cardinality)列,所谓低基数列就是指这个列只有很少的可取值。

位图索引的问题:

采用位图索引，一个键指向多行，可能数以百计甚至更多。如果更新一个位图索引键，那么这

个键指向的数百条记录会与你实际更新的那一行一同被有效地锁定。

位图索引的适用场景:

位图索引是针对那些值不经常改变的字段的，在实际应用中，如果某个字段的值需要频繁更新，

那么就不适合在它上面创建位图索引。

以下为针对位图索引的实验:

1.准备环节:

–创建一个表t

SQL> create table t(processed_flag varchar2(1));

表已创建。

–在表t的processed_flag创建一个位图索引

SQL> create bitmap index t_idx on t(processed_flag);

索引已创建。

2.在一个sql*plus会话中插入一行列值为N的记录，且先不commit；然后在另一个sql*plus会话中也

插入一行列值为N的记录，这时发现后面的会话会被阻塞。

–session1

SQL> insert into t values(‘N’);

已创建 1 行。

insert后不提交，则位图索引中的N键被锁住，任何DML语句只要与N有关都被阻塞

–session2

SQL> insert into t values(‘N’);

该语句被挂起

–查询lock情况

这里有两个锁是正常的，因为任何DML操作都会产生锁，因为两个DML现在都没有commit，所以都会持有锁。

关键是看两个session的等待事件。可以看到前面的sid=146等待的是SQL*Net message from client，实际是

处于空闲等待，而sid=147等待的是enq: TX – row lock contention，其实是在等待sid=146释放其持有的锁

两个session持有的锁都为3-SX(Row-X)行级排它锁。

select t2.username,

t3.owner,

t3.object_name,

t2.machine,

t2.sid as sid,

t2.serial#,

t2.LAST_CALL_ET,

t2.program,

t1.locked_mode

from v$locked_object t1, v$session t2, dba_objects t3

where t1.session_id = t2.sid

and t1.OBJECT_ID = t3.object_id

order by t2.logon_time;

USERNAME OWNER    OBJE MACHINE                         SID    SERIAL# LAST_CALL_ET PROGRAM    LOCKED_MODE

——– ——– —- —————————— —- ———- ———— ————– ————

SCOTT    SCOTT    T    WORKGROUP\PC-5       146          5          897 sqlplus.exe  3

SCOTT    SCOTT    T    WORKGROUP\PC-5       147         12          894 sqlplus.exe  3

已选择2行。

–列出指定 ID 的等待事件

可见前面的session sid=146在等待客户端输入，实际上就是等待commit命令，而sid=147则是在等待enq: TX – row lock contention

select SID,EVENT from v$session_wait where sid=147;

SQL> select SID,EVENT from v$session_wait where sid=147;

SID EVENT

—- —————————————————————-

147 enq: TX – row lock contention

已选择 1 行。

SQL> select SID,EVENT from v$session_wait where sid=146;

SID EVENT

—- —————————————————————-

146 SQL*Net message from client

已选择 1 行。

–查看当前被锁的 session 正在执行的 sql 语句

select  /*+  NO_MERGE(a)  NO_MERGE(b)  NO_MERGE(c)  */  a.username,  a.machine,  a.sid,

a.serial#, a.last_call_et “Seconds”, b.id1, c.sql_text “SQL”

from v$session a, v$lock b, v$sqltext c

where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value

USERNAME MACHINE                         SID    SERIAL#    Seconds        ID1 SQL

——– —————————— —- ———- ———- ———- ———————-

SCOTT    WORKGROUP\PC-5       147         12       3006     insert into t values(‘N’)

–session1 commit后，session2不再等待

SQL> insert into t values(‘N’);

已创建 1 行。

3.在一个sql*plus会话中插入一行列值为N的记录，且先不commit；然后在另一个sql*plus会话中

插入一行列值为Y的记录，这时发现二者互不影响。

–session1

SQL> insert into t values(‘N’);

已创建 1 行。

insert后不commit

–session2

SQL> insert into t values(‘Y’);

已创建 1 行。

insert后不commit

–查看lockqingk

可见两个session都持有锁，那么二者的等待事件是什么呢

select t2.username,

t3.owner,

t3.object_name,

t2.machine,

t2.sid as sid,

t2.serial#,

t2.LAST_CALL_ET,

t2.program

from v$locked_object t1, v$session t2, dba_objects t3

where t1.session_id = t2.sid

and t1.OBJECT_ID = t3.object_id

order by t2.logon_time;

USERNAME OWNER    OBJE MACHINE                         SID    SERIAL# LAST_CALL_ET PROGRAM

——– ——– —- —————————— —- ———- ———— ————–

SCOTT    SCOTT    T    WORKGROUP\PC-5       147         12          114 sqlplus.exe

SCOTT    SCOTT    T    WORKGROUP\PC-5       146         23          108 sqlplus.exe

已选择2行。

–查看等待事件

可见两个session都处于空闲等待

SQL> select SID,EVENT from v$session_wait where sid=146;

SID EVENT

—- —————————————————————-

146 SQL*Net message from client

已选择 1 行。

SQL> select SID,EVENT from v$session_wait where sid=147;

SID EVENT

—- —————————————————————-

147 SQL*Net message from client

已选择 1 行。