Oracle Bitmap Index __oracle

Bitmap indexes apply to low cardinality (low-cardinality) columns, and the so-called low cardinality column means that the column has very few desirable values.

Problem with bitmap indexing:
With a bitmap index, one key points to multiple rows, possibly hundreds or more. If you update a bitmap index key, then this
The hundreds of records that the key points to will be effectively locked together with the line you are actually updating.

The applicable scenario for bitmap indexes:
Bitmap indexes are for fields whose values don't change frequently, and in practice, if the value of a field needs to be updated frequently,
Then it is not appropriate to create a bitmap index on it.

The following is an experiment for bitmap indexing :
1. Preparatory process:
--Create a table t
Sql> CREATE TABLE T (Processed_flag varchar2 (1));

Table has been created.

--Create a bitmap index in the Processed_flag of table T
Sql> Create bitmap index t_idx on T (Processed_flag);

The index has been created.

2. Insert a record with a row value of N in a sql*plus session, and do not commit first, and then in another Sql*plus session
Insert a record with a row value of N, and then discover that the following session will be blocked.
--session1
Sql> INSERT into t values (' N ');

1 lines have been created.
If you do not commit after the insert, the N key in the bitmap index is locked and any DML statement is blocked as long as it is related to n

--session2
Sql> INSERT into t values (' N ');
The statement is suspended

--Query lock condition
There are two locks that are normal, because any DML operation will produce a lock because two DML are now not commit, so they will hold the lock.
The key is to look at two session waiting events. Can see the front of the sid=146 waiting is sql*net message from client, is actually
In idle waiting, and sid=147 waiting is Enq:tx-row lock contention, is waiting for sid=146 to release its holding lock
Two session-held locks are 3-sx (row-x) row-level lock.
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-200904261625 146 5 897 Sqlplus.exe 3
Scott Scott T workgroup\pc-200904261625 147 894 Sqlplus.exe 3

2 rows have been selected.

--Lists the wait events for the specified ID
The preceding session sid=146 is waiting for the client to input, which is actually waiting for a commit, while sid=147 is waiting for 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 rows have been selected.

Sql> Select Sid,event from v$session_wait where sid=146;

SID EVENT
---- ----------------------------------------------------------------
146 Sql*net Message from client

1 rows have been selected.

--View the currently locked session being executed SQL statement
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-200904261625 147 3006 327724 insert INTO t values (' N ')

After--session1 commit, Session2 no longer waits
Sql> INSERT into t values (' N ');

1 lines have been created.

3. Insert a record with a row value of N in a sql*plus session, and do not commit first; then in another Sql*plus session
When you insert a record with a row value of Y, you find that the two do not affect each other.
--session1
Sql> INSERT into t values (' N ');

1 lines have been created.
Do not commit after insert

--session2
Sql> INSERT into t values (' Y ');

1 lines have been created.
Do not commit after insert

--View Lockqingk
See two sessions are holding locks, then what is the waiting event of the two?
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-200904261625 147 114 Sqlplus.exe
Scott Scott T workgroup\pc-200904261625 146 108 Sqlplus.exe

2 rows have been selected.

--View Wait events
Visible two sessions are idle waiting
Sql> Select Sid,event from v$session_wait where sid=146;

SID EVENT
---- ----------------------------------------------------------------
146 Sql*net Message from client

1 rows have been selected.

Sql> Select Sid,event from v$session_wait where sid=147;

SID EVENT
---- ----------------------------------------------------------------
147 Sql*net Message from client has selected 1 rows.