Original: http://www.itpub.net/showthread.php? Threadid = 114023 & Pagenumber =
1: Bitmap index is segmented, that is, many records may be divided into N-section to store, that is, N begin / end, when new record INSERT uses the previously used physical address When it is time, a Bitmap section will be produced, even if there is only one record 2: When a record is deleted, a delete tag is made in the Bitmap index and tagged with a new record. Please see the specific demonstration 3: When the DML occurs, the record of the storage of a value stored in a value of the ROWID. Refer to the LOCK below ROW, this obviously affects the concurrent SQL> Create Table TN (a Number, B Number); Table Created .SQL> insert into tn select rownum, mod (rownum, 5) from all_objects where rownum <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 ........................... 0Total Blocks ... ................................................ .................................................................... ............... 114688Last Used ext fileid .................... 3last used blockid ....... ............ 1954Last Used Block .................................................................................. 2PL / SQL Procedure SuccessFully Completed.SQL> SELECT * from TN; A b ---------- ---------- 1 12 23 34 45 06 17 28 39 410 011 1A B --- ---------------- 12 213 314 415 016 117 218 319 420 020 Rows SELECTED.SQL> ALTER SYSTEM DUMP DATAFILE 3 block 1955; system altered.block header dump: 0x00c007a3object ID on ON Block? YSEG / OBJ: 0X00.18A0D77 ITC: 2 FLG: - TYP: 2 - Indexfsl: 0 fnx: 0x0 Ver: 0x01itl Xid Uba flag LCK SCN / FSC0X01 XID: 0x0000.000.00000000 UBA: 0x00000000.0000.00 - - 0 FSC 0x0000.000000000x02 xid: 0x0002.040.000000000000.00 ---- 0 FSC 0x0000.0000000000 ===================== = 0x7826C5CKDXCOLEV 0kDXCOLOK 0kdxcoopc 0x80: opcode = 0: IoT flags =
--- is converted = Ykdxconco 4kdxcosdc 0kdxconro 5kdxcofbo 46 = 0x2ekdxcofeo 7918 = 0x1eeekdxcoavs 7872kdxlespl 0kdxlende 0kdxlenxt 0 = 0x0kdxleprv 0 = 0x0kdxledsz 0kdxlebksz 8036row # 0 [8013] flag: -----, lock: 0col 0; len 1; (1 : 80 --- Represents the value of 0 col 1; LEN 6; (6): 00 c0 7e 03 00 00 --- RowID start point BLOCK and line number COL 2; LEN 6; (6): 00 c0 7e 03 00 17 --- RowId ended block and line number, pay attention to 17 = 16 7 = 23, that is, the effective position after the following conversion proceeds to 23bit Col 3; LEN 4; (4): CA 10 42 08 --- This value is converted to 11001010 (first byte does not represent RowID information) 00010000 01000010 00001000, and one from the starting point to the end point indicates that this value exists, there is a problem that must be noted here, this conversion The latter position is not a real physical location. In each byte internal bit, it is necessary to reverse the order. The first-byte does not represent location information, which is said that the above should be converted to 00001000 01000010 00010000, found that there is a value of every 5 0 record row # 1 [7990] flag: -----, lock: 0col 0; len 2; (2): C1 02 --- represents 1 COL 1; LEN 6; (6): 00 C0 7e 03 00 00col 2; LEN 6; (6): 00 c0 7e 03 00 0F ---- Note that this is f, that is, a total of only 16 bits, because 1 is the first record start, in the position of 16 There are already 5 col3; LEN 3; (3): C9 21 84 Note that the 21 84 here is 16-bit, and the rule conversion is 10000100 0010000001, 4 1, according to the rule conversion described above, just means recording ROW # 2 [7966] Flag: -----, Lock: 0col 0; LEN 2; (2): C1 03 --- Represents value of 2 col 1; LEN 6; (6): 00 c0 7e 0 3 00 00 Col 2; LEN 6; (6): 00 c0 7e 03 00 17Col 3; LEN 4; (4): CA 42 08 01ROW # 3 [7942] FLAG: -----, Lock: 0col 0; LEN 2; (2): C1 04 --- represents a value of 3 col 1; LEN 6; (6): 00 c0 7e 03 00 c 2; LEN 6; (6): 00 c0 7e 03 00 17col 3; LEN 4 (4): CA 84 10 02ROW # 4 [7918] Flag: ----- Lock: 0Col 0; LEN 2; (2): C1 05 --- Represents the value of 4 COL 1; LEN 6; 6): 00 c0 7e 03 00 00 COL 2; LEN 6; (6): 00 c0 7e 03 00 17Col 3; LEN 4; (4): CA 08 21 04 ----- End of Leaf Block Dump - --- Dump Data Blocks TSN: 2 File #: 3 minblk 1955 MaxBLK 1955SQL> Delete from Tn where a = 2; 1 row deleted.sql> commit; commit completed data.sql> ALter System Dump DataFile 3 block 1955; System Altered .Sql>
Block header dump: 0x00c007a3Object id on Block Yseg / obj: 0x66da csc: 0x00.18a0d77 itc: 2 flg: -? Typ: 2 - INDEXfsl: 0 fnx: 0x0 ver: 0x01Itl Xid Uba Flag Lck Scn / Fsc0x01 xid: 0x0000.000.00000000 Uba: 0x00000000.0000.00 ---- 0 FSC 0x0000.000000000X02 XID: 0x0003.047.000000E9 UBA: 0x00800DBA.00D9.1F --U- 2 FSC 0x001A.018A0D7DLLAF block dump ============ === header address 125987932 = 0x7826c5ckdxcolev 0kdxcolok 0kdxcoopc 0x80: opcode = 0: iot flags = --- is converted = Ykdxconco 4kdxcosdc 0kdxconro 6kdxcofbo 48 = 0x30kdxcofeo 7894 = 0x1ed6kdxcoavs 7846kdxlespl 0kdxlende 1kdxlenxt 0 = 0x0kdxleprv 0 = 0x0kdxledsz 0kdxlebksz 8036row # 0 [8013 ] Flag: -----, Lock: 0col 0; LEN 1; (1): 80Col 1; LEN 6; (6): 00 c0 7e 03 00 00 c 2; LEN 6; (6): 00 c0 7e 03 00 17Col 3; Len 4; (4): CA 10 42 08ROW # 1 [7990] Flag: -----, lock: 0col 0; len 2; (2): C1 02COL 1; LEN 6; (6) : 00 c0 7e 03 00 00col 2; LEN 6; (6): 00 c0 7e 03 00 0fcol 3; LEN 3; (3): C9 21 84ROW # 2 [7894] Flag: -----, Lock: 2 --- This is the copy after deletion, and we have discovered that the line has stopped Lock: 2 COL 0; LEN 2; (2): C1 03COL 1; LEN 6; (6): 00 c0 7e 03 00 00 Col 2; LEN 6; (6): 00 c0 7e 03 00 17Col 3; LEN 4; (4): CA 40 08 01 - We found that CA 42 has become CA 40, which is already a bit of BIT, it is exactly the removal of the record ROW # 3 [7966] Flag: --- D-, LOCK: 2 - - Here we found that the records of the value of 2 have deleted - D-, D represents delete col 0; LEN 2; (2): C1 03COL 1; LEN 6; (6): 00 c0 7e 03 00 00col 2; LEN 6; (6): 00 c0 7e 03 00 17Col 3; LEN 4; (4): CA 42 08 01ROW # 4 [7942] FLAG: -----, Lock: 0col 0; LEN 2; (2): C1 04COL 1; LEN 6; (6): 00 c0 7e 03 00 00 2 2; LEN 6; (6): 00 c0 7e 03 00 17Col 3; LEN 4; (4): CA 84 10 02ROW # 5 [7918] FLAG: ----- Lock: 0Col 0; LEN 2; (2): C1 05COL 1; LEN 6; (6): 00 c0 7e 03 00 00 2; LEN 6;
(6): 00 c0 7e 03 00 17Col 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 continues to supplement
First Truncate Table All Data Truncate Table Tn; SQL> EXEC SHOW_SPACE ('TN_bitmap', 'I'); Free Blocks ......................... .... 0Total Blocks ................................ 16Total Bytes .............. ............................................................................................ 14Unused bytes .... ........................ 114688Last Used ext fileid .................... 3Last Used Ext blockid ............................................. 2PL / SQL Procedure SuccessFully Completed. You can see that the index is empty and then inserted into a data 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: 2col 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 00 2; LEN 6 (6): 00 C0 7e 03 00 07Col 3; LEN 1; (1): 00ROW # 1 [8030] Flag: --- D- Lock: 2col 0; Nullcol 1; Nullcol 2; Nullcol 3; Nullsql> INSERT INTO TN VALUES (1, 1); 1 row created.sql> commit; commit completed.row # 0 [8009] Flag: --- D-, LOCK: --- D-, LOCK: --- D-, LOCK: 2 - - Tag deletion, one of the following is copied COL 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 c 2; LEN 6; (6): 00 c0 7e 03 00 07Col 3; LEN 1; (1): 00ROW # 1 [7987] FLAG: -----, Lock: 2col 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 00 00 --- 07 Exactly, 8 rowscol 2; LEN 6; (6): 00 C0 7e 03 00 07Col 3; LEN 2; (2): C8 03 - 03 Exactly, 2 records are inserted into SQL> INSERT INTO TN VALUES (1); 1 Row Created.SQL> ALTER System Dump DataFile 3 Block 1955; System Altered.Row # 0 [7987] Flag: --- D-, LOCK: 2COL 0; LEN 2; 2): C1 02COL 1; LEN 6; (6): 00 C0 7e 03 00 c 2; LEN 6; (6): 00 c0 7e 03 00 07col 3; LEN 2; (2): C8 03ROW # 1 [7965 ] Flag: -----, Lock: 2Col 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 C0 7e 03 00 c 2; LEN 6;
(6): 00 c0 7e 03 00 07col 3; LEN 2; (2): C8 07 - 07 Exactly, 3 records are inserted into SQL> INSERT INTO TN VALUES (1, 1); 1 row created.sql> INSERT INTO TN VALUES (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 completed .sql> ALTER System Dump DataFile 3 block 1955; system altered. We have inserted 5 records in the same transaction in the same session, found in Bitmap Actually made 5 copy row # 0 [7987] Flag: --- D-, LOCK: 2COL 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 c 2 2 LEN 6; (6): 00 c0 7e 03 00 07Col 3; LEN 2; (2): C8 03ROW # 1 [7965] FLAG: --- D-, LOCK: 2COL 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 00 Col 2; LEN 6; (6): 00 c0 7e 03 00 07col 3; LEN 2; (2): C8 07ROW # 2 [7943] Flag: --- D-, LOCK: 2COL 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 c 2; LEN 6; (6): 00 c0 7e 03 00 07col 3; LEN 2; (2): C8 0FROW # 3 [7921] Flag: --- D-, LOCK: 2Col 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 00col 2; LEN 6; (6): 00 c0 7E 03 00 07Col 3; LEN 2; (2): C8 1Frow # 4 [7899] Flag: --- D-, LOCK: 2COL 0; LEN 2; (2): C1 02COL 1; LEN 6; (6) : 00 c0 7e 03 00 00 Col 2; LEN 6; (6): 00 c0 7e 03 00 07Col 3; LEN 2; (2): C8 3Frow # 5 [7877] Flag: --- D-, LOCK: 2COL 0 Leen 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 00 Col 2; LEN 6; (6): 00 c0 7e 03 00 07col 3; LEN 2; (2): C8 7Frow # 6 [7855] Flag: ----- Lock: 2col 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 c0 7e 03 00 00 2; LEN 6; (6; : 00 c0 7e 03 00 07col 3; LEN 2; (2): C8 ff - ff just indicates that 8 records are inserted into SQL> INSERT INTO TN VALUES (1); 1 row created.sql> commit; commit; commit Complete.sql> ALTER SYSTEM DUMP DATAFILE 3 block 1955; system altered.sql> - Previous Bitmap segment store represents 8 records,
We will then insert the 9th record and then look at ROW # 0 [7855] Flag: ----- Lock: 2col 0; LEN 2; (2): C1 02Col 1; LEN 6; (6): 00 c0 7e 03 00 00 00 2 2; (6): 00 c0 7e 03 00 07col 3; len 2; (2): C8 FF - 8 records are full, and also remove the top 8 of the top 8 pieces to remove it. Row # 1 [7834] FLAG: ----- Lock: 2col 0; LEN 2; (2): C1 02COL 1; LEN 6; (6): 00 C0 7e 03 00 08Col 2; LEN 6; (6) ): 00 c0 7e 03 00 0fcol 3; LEN 1; (1): 00 Newly inserted Article 9 Records The new from 08 - 0F These 8 bytes of storage can be seen that the above experiment can be seen that when When the single INSERT occurs, it will be stored as a Bitmap Row in 8, which is just a Bit, and even if INSERT in the same transaction will result in a large number of copies and LOCK generation, seriously affect performance, and even Possible problems such as row migration, so we should not adopt Bitmap Index in a table that often changes, when Update occurs, it is more complicated, and it is not discussed for the time being. Transfer yangtingkun's post
http://itpub.net/showthread.php?threadid=115221sql> Truncate Table TN; Table has been cut off. SQL> EXEC SHOW_SPACE ('TN_bitmap', User, 'Index'); Free Blocks ........................... 0 Total Blocks ............................ 3 Total Bytes ................. ........ 12288 unused blocks ......................... 1 unused bytes ....... ..................... 4096 Last Used Ext FileID .................... 1 Last Used EXT Blockid ................. 26474 Last Used block ....................... 2 The PL / SQL process has been successfully completed. SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 26475; The system has changed. Leaf block dump =============== Header address 83060828 = 0x4f3685c kdxcolev 0 kdxcolev flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode = 0: IoT flags = --- is converted = Y kdxconco 4 kdxcosdc 0 kdxconro 0 kdxcofbo 36 = 0x24 kdxcofeo 3940 = 0xf64 kdxcoavs 3904 kdxlespl 0 kdxlende 0 kdxlenxt 0 = 0x0 kdxleprv 0 = 0x0 kdxledsz 0 kdxlebksz 3940 ----- end of leaf block dump ----- End dump data Blocks TSN: 0 File #: 1 minblk 26475 MaxBLK 26475 Index has been emptied SQL> Insert Into TN VALUES (1, 1); 1 line has been created. SQL> / has been created 1 line. SQL> / has been created 1 line. SQL> / has been created 1 line. SQL> / has been created 1 line. SQL> / has been created 1 line. SQL> / has been created 1 line. SQL> / has been created 1 line. SQL> / has been created 1 line. SQL> / has been created 1 line. Insert 10 lines of data in turn. SQL> Commit; submit completion. SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 26475; The system has changed.
Leaf block dump =============== Header address 83060828 = 0x4f3685c kdxcolev 0 kdxcolev flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode = 0: IoT flags = --- is converted = Y kdxconco 4 kdxcosdc 0 kdxconro 11 kdxcofbo 58 = 0x3a kdxcofeo 3716 = 0xe84 kdxcoavs 3658 kdxlespl 0 kdxlende 9 kdxlenxt 0 = 0x0 kdxleprv 0 = 0x0 kdxledsz 0 kdxlebksz 3940 row # 0 [3913] flag: --- D-, lock: 2 col 0; LEN 2; (2): C1 02 COL 1; LEN 6; (6): 00 40 67 68 00 00 COL 2; LEN 6; (6): 00 40 67 68 00 07 COL 3; LEN 1; 1): 00 row # 1 [3891] Flag: --- D-, LOCK: 2 COL 0; LEN 2; (2): C1 02 col 1; LEN 6; (6): 00 40 67 68 00 00 COL 2; LEN 6; (6): 00 40 67 68 00 07 COL 3; LEN 2; (2): C8 03 ROW # 2 [3869] Flag: --- D-, LOCK: 2 COL 0; LEN 2; (2): C1 02 COL 1; LEN 6; (6): 00 40 67 68 00 00 COL 2; LEN 6; (6): 00 40 67 68 00 07 COL 3; LEN 2; (2): C8 07 Row # 3 [3847] FLAG: --- D-, LOCK: 2 COL 0; LEN 2; (2): C1 02 COL 1; LEN 6; (6): 00 40 67 68 00 00 COL 2; LEN 6 (6): 00 40 67 68 00 07 COL 3; LEN 2; (2): C8 0F ROW # 4 [3825] FLAG: --- D-, LOCK: 2 COL 0; LEN 2; (2): C1 02 COL 1; LEN 6; (6): 00 40 67 68 00 00 COL 2; LEN 6; (6): 00 40 67 68 00 07 COL 3; LEN 2; (2): C8 1F Row # 5 [3803] FLAG: --- D-, LOCK: 2 COL 0; LEN 2; (2): C1 02 COL 1; LEN 6; (6): 00 40 67 68 00 00 COL 2; LEN 6 (6): 00 40 67 68 00 07 COL 3; LEN 2; (2): C8 3F ROW # 6 [3781] Flag: --- D-, LOCK: 2 COL 0; LEN 2; (2): C1 02 COL 1; LEN 6; (6): 00 40 67 68 00 00 COL 2; LEN 6; (6): 00 40 67 68 00 07 COL 3; LEN 2; (2): C8 7F ROW # 7 [ 3759] -----, LOCK: 2 COL 0; LEN 2; (2): C1 02 COL 1; LEN 6; (6): 00 40 67 68 00 00 COL 2; LEN 6; (6) : 00 40 67 68 00 07 COL 3; LEN 2; (2): C8 FF ROW # 8 [3738] FLAG: --- D-, LOCK: 2 COL 0; LEN 2; (2): C1 02 COL 1 Leen 6;
(6): 00 40 67 68 00 08 COL 2; LEN 6; (6): 00 40 67 68 00 0F COL 3; LEN 1; (1): 00 ROW # 9 [3716] Flag: ----- LOCK: 2 COL 0; LEN 2; (2): C1 02 COL 1; LEN 6; (6): 00 40 67 68 00 08 COL 2; LEN 6; (6): 00 40 67 68 00 0F COL 3 Len 2; (2): C8 03 ROW # 10 [3934] Flag: --- D-, LOCK: 2 COL 0; NULL COL 1; NULL COL 2; NULL COL 3; NULL ----- End of Of Leaf Block Dump ----- End Dump Data Blocks TSN: 0 File #: 1 Minblk 26475 MaxBLK 26475 Oracle indexes each inserted data. SQL> TRUNCATE TABLE TN; Table has been cut off. SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 26475; The system has changed. Leaf block dump =============== Header address 83060828 = 0x4f3685c kdxcolev 0 kdxcolev flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode = 0: IoT flags = --- is converted = Y kdxconco 4 kdxcosdc 0 kdxconro 0 kdxcofbo 36 = 0x24 kdxcofeo 3940 = 0xf64 kdxcoavs 3904 kdxlespl 0 kdxlende 0 kdxlenxt 0 = 0x0 kdxleprv 0 = 0x0 kdxledsz 0 kdxlebksz 3940 ----- end of leaf block dump ----- disposable insert 10 data SQL> INSERT INTO TN SELECT 1, 1 from User_Objects Where RownUM <11; 10 lines have been created. SQL> Commit; submit completion. SQL> ALTER SYSTEM DUMP DATAFILE 1 BLOCK 26475; The system has changed.
