Summary of data compression functions in Oracle

When you hear this word about Oracle Database compression, you must think of saving hard disk space. Yes, it is used to do this, but extending it out not only saves hard disk space, it can also save bandwidth and memory usage (cpu consumption is slightly reduced because more computing value operations are required, but in most environments the cpu is idle, so you can ignore it ).
Basic compression is provided from 9i, and 11gr1 provides the new compression function, namely OLTP compression. Basic compression is included in the EE version and is free of charge. However, OLTP compression requires an additional Oracle Advanced Compression option license. Advanced? Want to use? Pay for it.
The following figure helps you understand the principle of the compressed block:
 

Simply put, there will be a special data structure in the block, symbol table.
The content of Symbol table dump is as follows:
Tab 0, row 0, @ 0x1da4
Tl: 7 fb: -- H-FL -- lb: 0x0 cc: 12
Col 0: * NULL *
Col 1: [5] 56 41 4c 49 44
Col 2: [1] 4e
Col 3: * NULL *
Col 4: [1] 4e
Col 5: [1] 4e
Col 6: [3] 53 59 53
Col 7: [7] 78 6f 0a 1e 0d 39 19
Col 8: [19] 32 30 31 2d 31 30 2d 33 30 3a 31 32 3a 35 36 3a 32 34
Col 9: [2] c1 05
Col 10: [5] 49 4e 44 45 58
Col 11: [7] 78 6f 0a 1e 0d 39 19
Bindmp: 00 2a 0c 15 20 24 22 copy the real data of the Code as follows:
Tab 1, row 0, @ 0x1d5e
Tl: 16 fb: -- H-FL -- lb: 0x0 cc: 15
Col 0: * NULL *
Col 1: [5] 56 41 4c 49 44
Col 2: [1] 4e
Col 3: * NULL *
Col 4: [1] 4e
Col 5: [1] 4e
Col 6: [3] 53 59 53
Col 7: [7] 78 6f 0a 1e 0d 39 18
Col 8: [19] 32 30 31 2d 31 30 2d 33 30 3a 31 32 3a 35 36 3a 32 33
Col 9: [2] c1 02
Col 10: [5] 54 41 42 4c 45
Col 11: [7] 78 6f 0a 1e 0e 10 01
Col 12: [2] c1 03
Col 13: [5] 49 43 4f 4c 24
Col 14: [2] c1 15
Bindmp: 2c 00 05 0a 05 31 1e cd 49 43 4f 4c 24 ca c1 15 copy code is somewhat similar to the block structure of clustertable. Simply put, only the duplicate content of the block is stored in the symbol table. The real table row piece references the duplicate content stored in the symbol table.
Because Basiccompression and OLTPcompresssion are both compressed in blocks, even if they have duplicate content, but they cannot be compressed if they are not in the same block memory.
The principle is introduced here first. If you want to study it in depth, you can refer to the old bear's blog.
Oracle compressed table data block format analysis: http://www.laoxiong.net/dissect_compressed_block_part1.html
After completing the concept, we will briefly introduce several types of table compression:

1. Do not compress

Don't worry. I think it should be one of the types of table compression. To be more rigorous, it is listed as the first. Keyword: nocompress. It means not to compress. When you create a table by default, the system determines whether to compress the table based on the default attribute of your tablespace. If your tablespace is defined as default compress, you must specify nocompress when creating a non-compressed table in the tablespace. Example:
Create table t (id number) nocompress; copy the code
Related SQL (for test by yourself ):
Alter tablespace users default compress;
Select tablespace_name, compress_for from dba_tablespaces where tablespace_name = 'users ';
Create table ttt (id number) tablespace users;
Create table ttt2 (id number) tablespace users nocompress;
Select table_name, COMPRESS_FOR from user_tables where table_name in ('ttttt', 'ttt2 ');
Alter tablespace users default nocompress;
Select tablespace_name, compress_for from dba_tablespaces where tablespace_name = 'users'; copy code 2. Basic compression
Key Aspect 1: Data Compression takes effect only when direct path load is used. Common dml statements insert and update are not compressed.
Key Aspect 2: To ensure maximum compression, pctfree is automatically set to 0 (not mandatory. You can also set pctfree to another value when creating a table ).
Example:
Create table t (id number) compress;
Create table t (id number) compress basic; copy code 3. OLTP compression
Key Aspect 1: Unlike Basic compression, OLTP compression takes effect for all dml, so it is applicable to OLTP systems.
Key 2: Set pctfree to 10 automatically.
Key Aspect 3: The compression action in the oracle database is not generated in all dml operations. Only when the data in the new block reaches the limit will the compression operation in the block be triggered.
The third key point is described here:
Data blocks in the oracle database are compressed in batch mode, rather than each operation that changes the data will lead to compression operations.
When the reserved uncompressed data in the block reaches a limit, the compression operation in the block is triggered.
When a transaction causes the data in the block to reach the Jue value, all the contents in the block will be compressed. Then, more data is added to the block, and the entire block is compressed again to reach the maximum compression level. This process will repeat until the oracle database determines that it cannot get higher benefits from compression. Only some transactions that trigger the compression condition will experience a slight compression overhead.
Therefore, most oltp Transactions Act on the compressed block and have the same performance as uncompressed tables.
Only unlucky operations can cause intra-block compression.


For details, refer to the aco White Paper.

Example:
Create table t (id number) compress for oltp; copy Code 4. HCC (Hybrid Columnar Compression)


This feature is only available for Oracle databases in the Exadata all-in-one environment. It can be divided into four levels:
Query low
Query high
Archive low
Archive high
It is applicable to tables with infrequent changes or readonly. According to the level listed above, the corresponding compression ratio is getting higher and higher. As you can see, it can be up to 30 times, which is really amazing (of course this should be the best case ). Simple Description:

Unlike traditional data storage, all the information in a row is stored together. Instead, it uses the structure of CU (CompressionUnit) to store data, stores the information of each column in a group of (many rows) rows, and then compresses the data. This can save more space. (Originally, it was just a block reference, but now it is a multi-block reference, which definitely saves more space) But likewise, because of this storage structure, when one row of data is updated, locks all rows in a Logical Compression Unit.

I am not selling OracleDatabase, so I will introduce the content about HSCC here.

Finally, let's talk about the compression restrictions:

1. compress Basic and compress for oltp do not support information of Over 255 columns. (It really hurts, is it because the row with more than 255 columns will span multiple row piece ?)

2. BasicFile LOBs does not support compression, and SecureFile LOBs supports compression. (New things bring new bugs)

3. When compress basic is used, you cannot drop column. However, compress for oltp and various compression formats in Hcs are allowed.

4. the table cannot be indexed.

5. External tables or cluster tables are not supported (only stacked tables are supported ).

The content of the limitations of HSCC is not described. '

The following is a show_space (itpub improved version) script:

Create or replace procedure show_space (p_segname_1 IN VARCHAR2,
P_type_1 IN VARCHAR2 DEFAULT 'table ',
P_space IN VARCHAR2 DEFAULT 'auto ',
P_analyzed IN VARCHAR2 DEFAULT 'n ',
P_partition_1 IN VARCHAR2 default null,
P_owner_1 IN VARCHAR2 default user)
AUTHID CURRENT_USER
P_segname VARCHAR2 (100 );
P_type VARCHAR2 (30 );
P_owner VARCHAR2 (30 );
P_partition VARCHAR2 (50 );

Rochelle unformatted_blocks NUMBER;
Rochelle unformatted_bytes NUMBER;
Rochelle fs1_blocks NUMBER;
Rochelle fs1_bytes NUMBER;
Rochelle fs2_blocks NUMBER;
Rochelle fs2_bytes NUMBER;
Rochelle fs3_blocks NUMBER;
Rochelle fs3_bytes NUMBER;
Rochelle fs4_blocks NUMBER;
Rochelle fs4_bytes NUMBER;
Rochelle Blocks NUMBER;
Rochelle bytes NUMBER;

Rochelle free_blks NUMBER;
Rochelle total_blocks NUMBER;
Rochelle total_bytes NUMBER;
Rochelle unused_blocks NUMBER;
Rochelle unused_bytes NUMBER;
Rochelle lastusedextfileid NUMBER;
Rochelle lastusedextblockid NUMBER;
Rochelle last_used_block NUMBER;

PROCEDURE p (p_label IN VARCHAR2, p_num in number) IS
BEGIN
DBMS_OUTPUT.put_line (RPAD (p_label, 40, '.') | p_num );
END;
BEGIN
P_segname: = UPPER (p_segname_1 );
P_owner: = UPPER (p_owner_1 );
P_type: = p_type_1;
P_partition: = UPPER (p_partition_1 );

IF (p_type_1 = 'I' OR p_type_1 = 'I') THEN
P_type: = 'index ';
End if;

IF (p_type_1 = 'T' OR p_type_1 = 'T') THEN
P_type: = 'table ';
End if;

IF (p_type_1 = 'tp 'OR p_type_1 = 'tp') THEN
P_type: = 'table partition ';
End if;

IF (p_type_1 = 'IP' OR p_type_1 = 'IP') THEN
P_type: = 'index partition ';
End if;

IF (p_type_1 = 'C' OR p_type_1 = 'C') THEN
P_type: = 'cluster ';
End if;

DBMS_SPACE.UNUSED_SPACE (segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_type,
Partition_name => p_partition,
Total_blocks => l_total_blocks,
Total_bytes => l_total_bytes,
Unused_blocks => l_unused_blocks,
Unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

IF p_space = 'manual' OR (p_space <> 'auto' AND p_space <> 'auto') THEN
DBMS_SPACE.FREE_BLOCKS (segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_type,
Partition_name => p_partition,
Freelist_group_id => 0,
Free_blks => l_free_blks );

P ('free Blocks ', l_free_blks );
End if;

P ('total Blocks ', l_total_blocks );
P ('total Bytes ', l_total_bytes );
P ('unused Blocks ', l_unused_blocks );
P ('unused Bytes ', l_unused_bytes );
P ('Last Used Ext fileid', l_LastUsedExtFileId );
P ('Last Used Ext blockid', l_LastUsedExtBlockId );
P ('Last Used Block', l_LAST_USED_BLOCK );

/* IF the segment is analyzed */
IF p_analyzed = 'y' THEN
DBMS_SPACE.SPACE_USAGE (segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_type,
Partition_name => p_partition,
Unformatted_blocks => l_unformatted_blocks,
Unformatted_bytes => l_unformatted_bytes,
Fs1_blocks => l_fs1_blocks,
Fs1_bytes => l_fs1_bytes,
Fs2_blocks => l_fs2_blocks,
Fs2_bytes => l_fs2_bytes,
Fs3_blocks => l_fs3_blocks,
Fs3_bytes => l_fs3_bytes,
Fs4_blocks => l_fs4_blocks,
Fs4_bytes => l_fs4_bytes,
Full_blocks => l_full_blocks,
Full_bytes => l_full_bytes );
DBMS_OUTPUT.put_line (RPAD ('', 50 ,'*'));
DBMS_OUTPUT.put_line ('thesegment is analyzed ');
P ('0% -- 25% free spaceblocks ', l_fs1_blocks );
P ('0% -- 25% free spacebytes ', l_fs1_bytes );
P ('1970 -- 25% free spaceblocks ', l_fs2_blocks );
P ('1970 -- 25% free spacebytes ', l_fs2_bytes );
P ('1970 -- 50% free spaceblocks ', l_fs3_blocks );
P ('1970 -- 50% free spacebytes ', l_fs3_bytes );
P ('1970 -- 75% free spaceblocks ', l_fs4_blocks );
P ('1970 -- 75% free spacebytes ', l_fs4_bytes );
P ('unused Blocks ', l_unformatted_blocks );
P ('unused Bytes ', l_unformatted_bytes );
P ('total Blocks ', l_full_blocks );
P ('total bytes ', l_full_bytes );
End if;
END; copy code

Usage:

_ Sys @ FAKE> exec dexter. show_space ('basicc3', 't', 'auto', 'y ');
Total Blocks ......
Total Bytes...
Unused Blocks ......
Unused Bytes...
Last Used Ext FileId...
Last Used Ext BlockId... 584704
Last Used Block...
Thesegment is analyzed
0% -- 25% free spaceblocks ...... 0
0% -- 25% free spacebytes ...... 0
25% -- 50% free spaceblocks ...... 0
25% -- 50% free spacebytes ...... 0
50% -- 75% free spaceblocks ...... 0
50% -- 75% free spacebytes ...... 0
75% -- 100% free spaceblocks ...... 43
75% -- 100% free spacebytes ...... 352256
Unused Blocks ......
Unused Bytes...
Total Blocks ......
Total bytes...

PL/SQL procedure successfully completed.

_ Sys @ FAKE> exec dexter. show_space ('basicc2 ');
Total Blocks ......
Total Bytes...
Unused Blocks ......
Unused Bytes...
Last Used Ext FileId...
Last Used Ext BlockId... 584320
Last Used Block...

Script:
Select table_name, compress_for, compression, PCT_FREE, PCT_USED
From user_tables
Where table_name in ('basicc ', 'basicc2'); copy the code and note that the pct_free value is specified when compress is created. Otherwise, the result is not rigorous. In addition, you can use it with confidence during testing.
Create table basicc2 tablespace users compress as select * from dba_objects; copy the code create table as select to create a compressed table, because it uses direct pathwrite/read.