Research on the 9IR2 of Compress Table

Basic function introduction, please see the connection

http://otn.racle.com/oramag/oracle/04-mar/o24tech_data.html

Below, the internal storage details are discussed because the compression is units in block, so when the data itself does not exceed 1 block, if the data itself does not exceed 1 block, if it is not strict, if the following example, if the insertion data can be stored In block, it will not be compressed) SQL> Create Table Test1 (a varcha2 (20), b varcha2 (20), c varcha2 (20)) Compress; SQL> SELECT TABLESPACE_NAME, EXTENT_ID, Blocks from DBA_EXTENTS Where segment_name = 'Test1 '; TABLESPACE_NAME EXTENT_ID BLOCKS ------------------------------- ----- Users 0 8SQL> SELECT FILE_ID, Block_ID from DBA_EXTENTS WHERE Segment_Name = 'Test1'; file_id block_id ------------------ 11 769SQL> Insert / * append * / into test1 select 'AAAAAAAAAAAAAAAAAAAA', 'BBBBBBBBBBBBBBBBBBBB', 'CCCCCCCCCCCCCCCCCCCC' from dba_objects where rownum <1001; 1000 rows created.SQL> commit; Commit complete.SQL> alter system dump datafile 11 block min 769 block max 771; System altered.trace part data_block_dump, data header at 0xadb4674 ​​=============== tsiz: 0x1f88hsiz: 0x5cepbl: 0x0adb4674bdba: 0x02c0030276543210flag = -0 ------ ntab = 2nrow = 724frre = -1FSBO = 0x5Cefseo = 0x1127AVSP = 0xDtosp = 0xDR0_9IR2 = 0x0mec_kdbh9ir2 = 0x1R1_9ir2 = 0x076543 210FLAG_9IR2 = ------- CFCLS_9IR2 [4] = {0 32768 32768 32768} 0x1e: PTI [0] nrow = 1 OFFS = 00x22: PTI [1] nrow = 723 OFFS = 10x26: Pri [0] OFFS = 0x1f460x28: pri [1] offs = 0x1f410x2a: pri [2] offs = 0x1f3c0x2c: pri [3] offs = 0x1f37x5b6: pri [712] offs = 0x115e0x5b8: pri [713] offs = 0x11590x5ba: pri [714] offs = 0x11540x5bc: PRI [715] OFFS = 0x114f0x5be: pri [716] OFFS = 0x114a0x5c0: Pri [717] OFFS = 0x11450x5c2: pri [718] OFFS = 0x11400x5c4: pri [719] OFFS = 0x113b0x5c6: pri [720] OFFS =

0x11360x5c8: pri [721] OFFS = 0x11310x5ca: pri [722] OFFS = 0x112c0x5cc: PRI [723] OFFS = 0x1127 total record number 724, 5-byte storage block_row_dump: Tab 0, Row 0, @ 0x1f46tl: 66 FB: --H-FL - LB: 0x0 CC: 3Col 0: [20] 41 41 41 41 41 41 41 41 41 41 41 41 41Col 1: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 4243 43 43 43 43 43 43 43 43 43 43 43 43BindMP: 02 D3 03 DC 41 41 41 41 41 41 41 41 41 41 41 42 42 42 42 42 42 42 42 42 42 DC 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 is a centralized storage Tab 1, Row 0, @ 0x1f41TL: 5 FB: --H-FL - LB: 0x0 CC: 3COL 0: [20] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41Col 1: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42COL 2: [20] 43 43 43 43 43 43 43 43 43 43 43 43 43 43 4353 00 This 5 byte here is actual storage content, 03 represents compressed 3 fields, estimates that the last 00 here is said to the first store. Row 0 Tab 1, Row 1, @ 0x1f3ctl: 5 fb: --h-fl - lb: 0x0 CC: 3col 0: [20] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43BindMP: 2C 00 01 03 00Tab 1, Row 2, @ 0x1f37tl: 5 FB: --H-FL - lb: 0x0 cc: 3col 0: [20] 41 41 41 41 41 41 41 41 41 41 41 41 41 41COL 1: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 43 43BindMP: 2C 00 01 03 00Tab 1, Row 3, @

0x1f32tl: 5 FB: --H-fl - lb: 0x0 cc: 3col 0: [20] 41 41 41 41 41 41 41 41 41 41 41 41 41COL 1: [20] 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43BINDMP: 2C 00 01 03 00TAB 1 , Row 4, @ 0x1f2dl: 5 FB: --H-FL - LB: 0x0 CC: 3COL 0: [20] 41 41 41 41 41 41 41 41 41 41 41 41 41 41COL 1: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 43 43BindMP: 2C 00 01 03 00Tab 1, ROW 5, @ 0x1f28tl: 5 FB: --H-FL - LB: 0x0 CC: 3Col 0: [20] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42COL 2: [20] 43 43 43 43 43 43 43 43 43 43 43 43 43 43bindmp: 2C 00 01 03 00Tab 1, ROW 6, @ 0x1f23sql> Truncate Table Test1; Table Truncated.SQL> Conn Test / TestConnected.SQL> Insert / * Append * / INTO TEST1 Select Rownum, 'Bbbbbbb BBBBBBBBBBBBB ',' CCCCCCCCCCCCCCCCCCCC 'from dba_objects where rownum <1001; 1000 rows created.SQL> commit; Commit complete.SQL> alter system dump datafile 11 block min 769 block max 771; System altered.data_block_dump, data header at 0xadb4674 ​​=== ============ tsiz: 0x1f88hsiz: 0x5b2pbl: 0x0adb4674bdba: 0x02c0030276543210flag = -0 ------ ntab = 2nrow = 709frre = -1fsbo = 0x5b2fseo = 0x6e3avsp = 0xc5tosp = 0xc5r0_9ir2 = 0x0mec_kdbh9ir2 = 0x1r1_9ir2 = 0x076543210flag_9ir2 = ------ OCFCLS_9IR2 [3] = {0 32768 32768} Perm_9ir2 [3] = {2 0 1} This part is critical, indicating that the following actual storage field sequence is to say,

The order of the columns in the back physical store COL2, COL0, and COL1 corresponds to column in the data dictionary should be COL0, COL1, COL2 0x20: PTI [0] nrow = 1 OFFS = 00x24: PTI [1] nrow = 708 OFFS = 10x28: PRI [ 0] OFFS = 0x1f5b0x2a: pri [1] OFFS = 0x1f540x2c: pri [2] OFFS = 0x1f4d0x2e: pri [3] OFFS = 0x1f460x30: pri [4] OFFS = 0x1f3f0x32: pri [5] OFFS = 0x1f380x34: pri [6] offs = 0x1f310x36: pri [7] offs = 0x1f2a0x38: pri [8] offs = 0x1f23x25c: pri [282] offs = 0x15710x25e: pri [283] offs = 0x15680x260: pri [284] offs = 0x155f0x262: pri [285] offs = 0x15560x264: pri [286] offs = 0x154d0x266: pri [287] offs = 0x15440x268: pri [288] offs = 0x153b0x26a: pri [289] offs = 0x15320x26c: pri [290] offs = 0x15290x26e: pri [291] offs = 0x15200x270: PRI [292] OFFS = 0x1516Block_row_dump: Tab 0, Row 0, @ 0x1f5btl: 45 fb: --h-fl - lb: 0x0 CC: 2Col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43BindMP: 01 24 02 DC 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 DC 43 43 43 43 43 43 43 43 43 43 43 43 43 43TAB 1, ROW 0, @ 0x1f52tl: 9 FB : --H-fl - lb: 0x0 cc: 3col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 39Bindmp: 2C 00 02 02 00 CB 37 30 39 We note 02 here] 02 represents compressed 2 fields, the original inserted ROWNUM corresponds to data dictionary The first field in the middle table is stored in the last tab 1, Row 1, @ 0x1f49tl: 9 fb: - H-FL - LB: 0x0 CC: 3COL 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [3] 37 31 30BindMP: 2C 00 02 02 00 CB 37 31 30TAB 1, ROW 2, @

0x1F40TL: 9 FB: --H-FL - LB: 0x0 CC: 3col 0: [20] 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [3] 37 31 31Bindmp: 2C 00 02 02 00 C 37 31 31TAB 1, ROW 3, @ 0x1f37tl: 9 FB: --H -Fl - lb: 0x0 cc: 3col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [3] 37 37 37 32Bindmp: 2C 00 02 02 00 CB 37 31 32TAB 1, ROW 4, @ 0x1f2tl: 9 FB: --H-FL - lb: 0x0 CC : 3col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [3] 37 31 33BINDMP: 2C 00 02 02 00 CB 37 31 33TAB 1, ROW 5, @ 0x1f25tl: 9 fb: --h-fl - lb: 0x0 cc: 3col 0: [20] 42 42 42 42 42 42 42 42 42 42 42242 42 43 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [3] 37 31 34BINDMP: 2C 00 02 02 00 CB 37 31 34TAB 1, ROW 6, @ 0x1f 1CTL: 9 FB: --H-FL - LB: 0x0 CC: 3COL 0: [20] 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [3] 37 31 35BINDMP: 2C 00 02 02 00 C 37 31 35TAB 1, ROW 7, @ 0x1f13tl: 9 FB: --H -Fl - lb: 0x0 cc: 3col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [3] 37 31 36Sql> Drop Table Test1; Table Dropped.sql> Create Table Test1 (a varchar2 (20), b varchar2 (20), c varcha2 (20), D varchar2 (10)) Compress; table created.sql> INSERT / * APPEND * / INTO TEST1 SELECT ROWNUM, 'BBBBBBBBBBBBBBBBBBBBBB', 'CCCCCCCCCCCCCCCCCCCCCC', '

DDDDDD 'from dba_objects where rownum <1001; 1000 rows created.SQL> commit; Commit complete.SQL> select file_id, block_id from dba_extents where segment_name =' TEST1 '; FILE_ID BLOCK_ID ---------- --- ------ 11 769SQL> Conn Test / TestConnected.SQL> ALTER SYSTEM DUMP DATAFILE 11 block min 769 block max 771; system altered.data_block_dump, data header at 0xADB4674 ============ === tsiz: 0x1f88hsiz: 0x5d4pbl: 0x0adb4674bdba: 0x02c0030276543210flag = -0 ------ ntab = 2nrow = 725frre = -1fsbo = 0x5d4fseo = 0x64cavsp = 0xctosp = 0xcr0_9ir2 = 0x0mec_kdbh9ir2 = 0x1r1_9ir2 = 0x076543210flag_9ir2 = ------ OCFCLS_9IR2 [4] = {0 32768 32768 32768} Perm_9ir2 [4] = {3 0 1 2} This part is critical, indicating that the following actual storage field sequence is to say, the order of physical storage, col3, col0, col1, col2 The order in the corresponding data dictionary should be col0, col1, col2, col3 0x22: PTI [0] nrow = 1 OFFS = 00x26: PTI [1] nrow = 724 OFFS = 10x2a: pri [0] OFFS = 0x1f540x2c: PRI [ 1] OFFS = 0x1f4d0x2e: Pri [2] OFFS = 0x1f460x30: pri [3] OFFS = 0x1f3f0x32: pri [4] OFFS = 0x1f380x34: pri [5] OFFS = 0x1f310x36: pri [6] OFFS = 0x1f2a0x244: pri [269] OFFS = 0x15df0x246: Pri [270] OFFS = 0x15d60x248: Pri [271] offs = 0x15cd0x24a: pri [272] offs = 0x15c40x24c: pri [273] offs = 0x15bb0x24e: pri [274] offs = 0x15b20x250: pri [275] offs = 0x15a90x252: pri [276] offs = 0x159fblock_row_dump: tab 0, Row 0, @

0x1F54TL: 52 FB: --H-FL - LB: 0x0 CC: 3COL 0: [20] 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43 43 43COL 2: [6] 44 44 44 44 44 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 44 44 44 44 44 44 44TAB 1, ROW 0, @ 0x1f4btl: 9 Fb: --H-FL - LB: 0x0 CC: 4Col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [6] 44 44 44 44 44 44COL 3: [3] 37 32 35BINDMP: 2C 00 02 03 00 CB 37 32 35TAB 1, ROW 1, @ 0x1f42tl: 9 FB: --H- FL - lb: 0x0 cc: 4col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 COL 2: [6] 44 44 44 44 44 44COL 3: [3] 37 32 36Bindmp: 2C 00 02 03 00 CB 37 32 36TAB 1, ROW 2, @ 0x1f39tl: 9 FB: - -H-fl - lb: 0x0 cc: 4col 0: [20] 42 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43COL 2: [6] 44 44 44 44 44 44COL 3: [3] 37 32 37Bindmp: 2C 00 02 03 00 CB 37 32 37TAB 1, ROW 3, @ 0x1f30tl: 9 FB: --H-FL - lb: 0x0 CC: 4col 0: [20 ] 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 43 43COL 2: [6 ] 44 44 44 44 44 44COL 3: [3] 37 32 38Bindmp: 2C 00 02 03 00 CB 37 32 38SQL> Truncate Table Test1; Table Truncated.SQL> Conn Test / TestConnected.SQL> INSERT / * APPEND * / INTO Test1 Select Rownum, 'Bbbbbbbbbbbbbbbbbbbb', 'ccccccccccccccccccccc', ROWNUM 1 from DBA_Objects Where Rownum <1001

1000 rows created.sql> commit; commit completed.sql> ALTER SYSTEM DUMP DATAFILE 11 block min 769 block max 771; system altered.data_block_dump, data header at 0xADB4674 =============== Tsiz : 0x1f88hsiz: 0x46epbl: 0x0adb4674bdba: 0x02c0030276543210flag = -0 ------ ntab = 2nrow = 547frre = -1fsbo = 0x46efseo = 0x477avsp = 0x9tosp = 0x9r0_9ir2 = 0x0mec_kdbh9ir2 = 0x1r1_9ir2 = 0x076543210flag_9ir2 = ------ OCfcls_9ir2 [3] = {0 32768 32768} Perm_9ir2 [4] = {2 0 1 3} This part is critical, indicating that the following actual storage field sequence is to say, the order of the subsequent physical storage COL2, COL0, COL1, COL3 correspondence data dictionary The order should be COL0, COL1, COL2, COL3 0x20: PTI [0] nrow = 1 OFFS = 00x24: PTI [1] nrow = 546 OFFS = 10x28: Pri [0] OFFS = 0x1f5b0x2a: pri [1] OFFS = 0x1f520x2c : PRI [2] OFFS = 0x1f490x2e: pri [3] OFFS = 0x1f400x30: Pri [4] OFFS = 0x1f370x32: pri [5] OFFS = 0x1f2e0x34: pri [6] OFFS = 0x1f25x464: pri [542] OFFS = 0x4ab0x466: Pri [543] OFFS = 0x49e0x468: Pri [544] OFFS = 0x4910x46a: pri [545] OFFS = 0x4840x46c: pri [546] OFFS = 0x477 block_row_dump: Tab 0, Row 0, @ 0x1f5btl: 45 fb: --h-fl --- LB: 0x0 cc: 2col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 4 2 42 43 43 43 43 43 43 43 43 43Bindmp: 02 47 02 DC 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43 43 43TAB 1, ROW 0, @ 0x1f52tl: 9 FB: --H-FL - LB: 0x0 CC: 4col 0: [20] 42 42 42 42 42 42 42 42 42 42 42 42 43 43 43 43 43 43 43 43 43 43 43COL 2: [1] 31COL 3: [1] 32BINDMP: 2C 00 03 02 00 C9 31 C9 32TAB 1, ROW 1, @

0x1f49tl: 9 fb: --h-fl - lb: 0x0 cc: 4col 0: [20] 42 42 42 42 42 42 42 42 42 42COL 1: [20] 43 43 43 43 43 43 43 43 43 43 43COL 2: [1] 32COL 3: [1] 33 For more detailed content and false content in bindmp, two groups or more groups in the same block The same data, ORACLE how to deal with the storage I have not done more in-depth research, this example only demonstrates that the minus data is a group, this example, in the same block, if some columns have the same data, Oracle stores a copy, and Place these column compressed pointers in the BINDMP front BINDMP back part of each of the uncomfortable values ​​that cannot be compressed. These columns and data dictionary are written by the block header's Perm_9ir2 [4] = {2 0 1 3} section describes such a compression table, in fact similar to the process of concentrated Cluster, cluster is a plurality of table sharing fields, and here It is a multi-line shared part field in Block. When the update compressed field occurs, the line will generate row migration, even if it is updated back to the original value, the line will not return to the original compressed block, but this time Bindmp Also record new row migration data address