Oracle Data Table Compression

Oracle Data Table compression principle: stores the repeated column values of all rows in the same Block in the header area. This region is called a symboltable ).

Oracle Data Table compression principle: stores the repeated column values of all rows in the same Block in the header area. This region is called a symbol table ).

Oracle Data Table Compression
Compression principle: store the repeated column values of all rows in the same Block in the header area. This region is called a symbol table ). If the value of a column in the row is equal
The value stored in the symbol table, instead of the actual value, only the address of the value in the symbol table.

Objects suitable for table compression:
Data Tables with duplicate column values in the Block;
Tables with more reads and fewer changes.

Advantages of data table compression:
Data Tables with duplicate column values in a Block can greatly save storage space and reduce IO and memory usage.
Disadvantages of data table compression:
The CPU needs to be compressed and compressed, resulting in additional consumption.
DBAs should have a good understanding of data distribution.

Create table
(A NUMBER (5 ),
B VARCHAR2 (30 ))
COMPRESS;

Create table B
(A NUMBER (5 ),
B VARCHAR2 (30 ));

Insert into a values (1, 'aaaaaaaaaaaaaaaaa ');
Insert into a values (2, 'aaaaaaaaaaaaaaa ');
Insert into a values (3, 'aaaaaaaaaaaaaaaaa ');

Insert into B values (1, 'aaaaaaaaaaaaaaaaa ');
Insert into B values (2, 'aaaaaaaaaaaaaaa ');
Insert into B values (3, 'aaaaaaaaaaaaaaaaa ');

Data_block_dump, data header at 0x2b3b07180264
====================
Tsiz: 0x1f98
Hsiz: 0x18
Pbl: 0x2b3b07180264
76543210
Flag = --------
Ntab = 1
Nrow = 3
Frre =-1
Fsbo = 0x18
Fseo = 0x1f53
Avsp = 0x1f3b
Tosp = 0x1f3b
0xe: pti [0] nrow = 3 offs = 0
0x12: pri [0] offs = 0x1f81
0x14: pri [1] offs = 0x1f6a
0x16: pri [2] offs = 0x1f53
Block_row_dump:
Tab 0, row 0, @ 0x1f81
Tl: 23 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 02
Col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
Tab 0, row 1, @ 0x1f6a
Tl: 23 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 03
Col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
Tab 0, row 2, @ 0x1f53
Tl: 23 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 04
Col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
End_of_block_dump
Block dump from cache:

Data_block_dump, data header at 0xf427c064
====================
Tsiz: 0x1f98
Hsiz: 0x18
Pbl: 0xf427c064
76543210
Flag = --------
Ntab = 1
Nrow = 3
Frre =-1
Fsbo = 0x18
Fseo = 0x1f53
Avsp = 0x1f3b
Tosp = 0x1f3b
0xe: pti [0] nrow = 3 offs = 0
0x12: pri [0] offs = 0x1f81
0x14: pri [1] offs = 0x1f6a
0x16: pri [2] offs = 0x1f53
Block_row_dump:
Tab 0, row 0, @ 0x1f81
Tl: 23 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 02
Col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
Tab 0, row 1, @ 0x1f6a
Tl: 23 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 03
Col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
Tab 0, row 2, @ 0x1f53
Tl: 23 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 04
Col 1: [16] 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
End_of_block_dump
Block dump from disk:

In this way, there is no difference in storage.

Recommended reading:

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store

,