Research on Bitmap Index

1: Bitmap indexes are stored in segments. That is to say, many records may be stored in N segments, that is, there are N in/end records, which should be divided by extent, if an extent is too large, will it score points?
When a new record is inserted and a previously unused physical address is used, a bitmap segment is generated for storage, even if there is only one record

2: When deleting a record, a delete mark is made on the bitmap index and marked with a new record. The following is a detailed demonstration.

3: When DML occurs, it will lock the record where the rowid of a bit stored in a value. Refer to the lock in the row below, which will obviously affect concurrency.

SQL> Create Table TN (a number, B number );

Table created.

SQL> insert into tn select rownum, MOD (rownum, 5) from all_objects where rownum & lt; 21;

20 rows created.

SQL> commit;

Commit complete.

SQL> Create bitmap index tn_bitmap on TN (B );

Index created.

SQL> exec show_space ('tn _ bitmap', user, 'index ');
Free blocks ......
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 1954
Last used block...

PL/SQL procedure successfully completed.

SQL> select * from TN;

A B
--------------------
1 1
2 2
3 3
4
5 0
6 1
7 2
8 3
9 4
10 0
11 1

A B
--------------------
12 2
13 3
14 4
15 0
16 1
17 2
18 3
19 4
20 0

20 rows selected.

SQL> alter system dump datafile 3 block 1955;

System altered.

Block header dump: 0x00c007a3
Object ID on block? Y
SEG/obj: 0x66da CSC: 0x00. 18a0d77 ITC: 2 flg:-Typ: 2-Index
FSL: 0 fnx: 0x0 Ver: 0x01

ITL Xid UBA flag lck scn/FSC
0x01 Xid: 0x0000. 000.00000000 UBA: 0x00000000. 0000.00 ---- 0 FSC 0x0000.00000000
0x02 Xid: 0x0002. 040.000000ea UBA: 0x00000000. 0000.00 ---- 0 FSC 0x0000.00000000

Leaf block dump
====================
Header address 125987932 = 0x7826c5c
Kdxcolev0
Kdxcolok 0
Kdxcoopc 0x80: opcode = 0: IOT flags = --- is converted = y
Kdxconco 4
Kdxcosdc 0
Kdxconro 5
Kdxcofbo 46 = 0x2e
Kdxcofeo 7918 = 0x1eee
Kdxcoavs 7872
Kdxlespl 0
Kdxlende 0
Kdxlenxt 0 = 0x0
Kdxleprv 0 = 0x0
Kdxledsz 0
Kdxlebksz8036
Row #0 [8013] flag: -----, lock: 0
Col 0; Len 1; (1): 80 --- indicates that the value is 0.
COL 1; Len 6; (6): 00 C0 7E 03 00 00 --- block and row number of the rowid start point
Col 2; Len 6; (6): 00 C0 7E 03 00 17 --- block and row number ending with rowid. Note that 17 = 16 + 7 = 23, that is, the valid position after the conversion is 23 bits.
Col 3; Len 4; (4): Ca 10 42 08 --- convert the value to 11001010 in hexadecimal notation (the first byte does not represent rowid information) 00010000 01000010 00001000,
If the value 1 from the start point to the end point exists, it is important to note that the converted location is not a real physical location, in each byte, bit must reverse the order. The first byte does not indicate the location information.
That is to say, the above should be converted to 00001000 01000010 00010000, and it is found that every five records have a value of 0.
Row #1 [7990] flag: -----, lock: 0
Col 0; Len 2; (2): C1 02 --- indicates the value is 1
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 0f --- Note that this is F, that is, there are only 16 bits in total, because 1 is the first record, there are already five records at the 16 position.
Col 3; Len 3; (3): C9 21 84 Note that the 21 84 here is exactly 16 bits. After the conversion according to the rules described above, the 10000100 bits and s exactly indicate the record
Row #2 [7966] flag: -----, lock: 0
Col 0; Len 2; (2): C1 03 --- indicates that the value is 2.
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 17
Col 3; Len 4; (4): Ca 42 08 01
Row #3 [7942] flag: -----, lock: 0
Col 0; Len 2; (2): C1 04 --- indicates 3
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 17
Col 3; Len 4; (4): Ca 84 10 02
Row #4 [7918] flag: -----, lock: 0
Col 0; Len 2; (2): C1 05 --- indicates that the value is 4
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 17
Col 3; Len 4; (4): Ca 08 21 04
----- End of leaf block dump -----
End dump data blocks TSN: 2 file #: 3 minblk 1955 maxblk 1955

SQL> Delete from TN where a = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 3 block 1955;

System altered.

SQL>

Block header dump: 0x00c007a3
Object ID on block? Y
SEG/obj: 0x66da CSC: 0x00. 18a0d77 ITC: 2 flg:-Typ: 2-Index
FSL: 0 fnx: 0x0 Ver: 0x01

ITL Xid UBA flag lck scn/FSC
0x01 Xid: 0x0000. 000.00000000 UBA: 0x00000000. 0000.00 ---- 0 FSC 0x0000.00000000
0x02 Xid: 0x0003. 047.000000e9 UBA: 0x00800dba. 00d9. 1f -- U-2 FSC 0x001a. 018a0d7d

Leaf block dump
====================
Header address 125987932 = 0x7826c5c
Kdxcolev0
Kdxcolok 0
Kdxcoopc 0x80: opcode = 0: IOT flags = --- is converted = y
Kdxconco 4
Kdxcosdc 0
Kdxconro 6
Kdxcofbo 48 = 0x30
Kdxcofeo 7894 = 0x1ed6
Kdxcoavs 7846
Kdxlespl 0
Kdxlende 1
Kdxlenxt 0 = 0x0
Kdxleprv 0 = 0x0
Kdxledsz 0
Kdxlebksz8036
Row #0 [8013] flag: -----, lock: 0
Col 0; Len 1; (1): 80
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 17
Col 3; Len 4; (4): Ca 10 42 08
Row #1 [7990] flag: -----, lock: 0
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 0f
Col 3; Len 3; (3): C9 21 84
Row #2 [7894] flag: -----, lock: 2 --- this is the copy after deletion. We found that the row has been locked at the time of deletion. Lock: 2
Col 0; Len 2; (2): C1 03
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 17
Col 3; Len 4; (4): Ca 40 08 01 --- we found that CA 42 has changed to ca 40, that is, one bit is missing, the record to be deleted.
Row #3 [7966] flag: --- D-, lock: 2 --- Here we find that records with the value of 2 have been deleted --- D-, d Indicates Delete
Col 0; Len 2; (2): C1 03
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 17
Col 3; Len 4; (4): Ca 42 08 01
Row #4 [7942] flag: -----, lock: 0
Col 0; Len 2; (2): C1 04
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 17
Col 3; Len 4; (4): Ca 84 10 02
Row #5 [7918] flag: -----, lock: 0
Col 0; Len 2; (2): C1 05
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 17
Col 3; Len 4; (4): Ca 08 21 04
----- End of leaf block dump -----
End dump data blocks TSN: 2 file #: 3 minblk 1955 maxblk 1955

First, all data in the truncate table
Truncate table tn;

SQL> exec show_space ('tn _ bitmap ',' I ');
Free blocks ......
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 1954
Last used block...

PL/SQL procedure successfully completed.

The index is empty.

Insert a data record.

SQL> insert into tn values (1, 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 3 block 1955;

System altered.

Row #0 [8009] flag: -----, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 1; (1): 00
Row #1 [8030] flag: --- D-, lock: 2
Col 0; null
COL 1; null
Col 2; null
Col 3; null

SQL> insert into tn values (1, 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 3 block 1955;

System altered.

Row #0 [8009] flag: --- D-, lock: 2 -- Mark deletion, the following copy
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 1; (1): 00
Row #1 [7987] flag: -----, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00 00---07 indicates 8 rows
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 03 -- 03 indicates that two records are inserted.

SQL> insert into tn values (1, 1 );

1 row created.

SQL> alter system dump datafile 3 block 1955;

System altered.

Row #0 [7987] flag: --- D-, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 03
Row #1 [7965] flag: -----, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 07 -- 07 indicates that three records are inserted.

SQL> insert into tn values (1, 1 );

1 row created.

SQL> insert into tn values (1, 1 );

1 row created.

SQL> insert into tn values (1, 1 );

1 row created.

SQL> insert into tn values (1, 1 );

1 row created.

SQL> insert into tn values (1, 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 3 block 1955;

System altered.

We inserted 5 Records consecutively in the same transaction in the same session and found that 5 copies were made in bitmap.

Row #0 [7987] flag: --- D-, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 03
Row #1 [7965] flag: --- D-, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 07
Row #2 [7943] flag: --- D-, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 0f
Row #3 [7921] flag: --- D-, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 1f
Row #4 [7899] flag: --- D-, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 3f
Row #5 [7877] flag: --- D-, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 7f
Row #6 [7855] flag: -----, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 ff -- FF indicates that eight records are inserted.

SQL> insert into tn values (1, 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 3 block 1955;

System altered.

SQL>

-- The storage of the previous bitmap segment indicates eight records. We can insert another 9th records to check them again.

Row #0 [7855] flag: -----, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 00
Col 2; Len 6; (6): 00 C0 7E 03 00 07
Col 3; Len 2; (2): C8 ff -- eight records are full, and the first eight copies are cleared.
Row #1 [7834] flag: -----, lock: 2
Col 0; Len 2; (2): C1 02
COL 1; Len 6; (6): 00 C0 7E 03 00 08
Col 2; Len 6; (6): 00 C0 7E 03 00 0f
Col 3; Len 1; (1): 00 the newly inserted 9th records are stored by the new 8 bytes from 08 -- 0f.

Based on the above experiment, we can see that when a single insert occurs, 8 records will be stored as a bitmap row, which is exactly a byte bit, and even
Insert in the same transaction can also lead to a large number of copies and locks, seriously affecting the performance, and even may have serious problems such as row migration, so in tables that often change
We should not use bitmap index. When update occurs, the situation is more complicated. We will not discuss it for the moment.