Oracle compression function summary 1-compression function Introduction

Oracle Database Compression

When you hear this word, you must have thought 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 ).

Basiccompression 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 additional Oracle Advanced Compression optionlicense. 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 Symboltable dump is as follows:

tab 0, row 0, @0x1da4tl: 7 fb: --H-FL-- lb: 0x0  cc: 12col  0:*NULL*col  1: [5]  56 41 4c 49 44col  2: [1]  4ecol  3:*NULL*col  4: [1]  4ecol  5: [1]  4ecol  6: [3]  53 59 53col  7: [7]  78 6f 0a 1e 0d 39 19col  8:[19]  32 30 31 31 2d 31 30 2d 33 30 3a 3132 3a 35 36 3a 32 34col  9: [2]  c1 05col 10: [ 5] 49 4e 44 45 58col 11: [ 7] 78 6f 0a 1e 0d 39 19bindmp: 00 2a 0c 15 20 24 22

The real data content is as follows:

tab 0, row 0, @0x1da4tl: 7 fb: --H-FL-- lb: 0x0  cc: 12col  0:*NULL*col  1: [5]  56 41 4c 49 44col  2: [1]  4ecol  3:*NULL*col  4: [1]  4ecol  5: [1]  4ecol  6: [3]  53 59 53col  7: [7]  78 6f 0a 1e 0d 39 19col  8:[19]  32 30 31 31 2d 31 30 2d 33 30 3a 3132 3a 35 36 3a 32 34col  9: [2]  c1 05col 10: [ 5] 49 4e 44 45 58col 11: [ 7] 78 6f 0a 1e 0d 39 19bindmp: 00 2a 0c 15 20 24 22 

It is 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.

Parsing the data block format of an Oracle compressed table:

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 ; 

Related SQL (for test by yourself ):

alter tablespace users default compress ;select tablespace_name , compress_for fromdba_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 fromuser_tables where table_name in ('TTT','TTT2') ;alter tablespace users default nocompress ;select tablespace_name , compress_for fromdba_tablespaces where tablespace_name='USERS' ;

2. Basic compression

L Point 1: Data Compression takes effect only when directpath load is used. Normal dml statements insert and update are not compressed.

L point 2: during compression, pctfree is automatically set to 0 to ensure maximum compression (not mandatory, or pctfree can be set to another value when creating a table ).

Example:

create table t(id number) compress ;create table t(id number) compress basic ;

3. OLTP compression

L key aspect 1: Unlike Basic compression, OLTPcompression takes effect for all dml, so it is applicable to OLTP systems.

L key 2: Automatically Set pctfree to 10. You can also set it yourself.

L key aspect 3: The compression action in the oracle database is not generated in all dml operations, compression in a block is triggered only when the data in the new block reaches the limit.

The third key point is described here:

Oracle Database compression data blocks in batch modeInstead of causing compression for every data change operation.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 ;

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:

L Query low

L Query high

L Archive low

L 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. InsteadCU (CompressionUnit)To store data, store the information of each column in a group of (many rows) rows, and then perform the compression operation. 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 foroltp do not support more than 255 columns of information. (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 SecureFileLOBs supports compression. (New things bring new bugs)

3. When Compress Basic is used, dropcolumn is not supported. However, compress for oltp and various compression formats in hcc are allowed.

4. the table cannot be indexed.

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

The content of the limitations of HSCC is not described.

Attachment 1 show_space script

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 DEFAULTUSER)  AUTHIDCURRENT_USER AS p_segname   VARCHAR2(100); p_type      VARCHAR2(30);  p_owner     VARCHAR2(30); p_partition VARCHAR2(50);  l_unformatted_blocks NUMBER; l_unformatted_bytes  NUMBER; l_fs1_blocks         NUMBER; l_fs1_bytes          NUMBER; l_fs2_blocks         NUMBER; l_fs2_bytes          NUMBER; l_fs3_blocks         NUMBER; l_fs3_bytes          NUMBER; l_fs4_blocks         NUMBER; l_fs4_bytes          NUMBER; l_full_blocks        NUMBER; l_full_bytes         NUMBER;  l_free_blks          NUMBER; l_total_blocks       NUMBER; l_total_bytes        NUMBER; l_unused_blocks      NUMBER; l_unused_bytes       NUMBER; l_LastUsedExtFileId  NUMBER; l_LastUsedExtBlockId NUMBER; l_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);   IFp_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('LastUsed Ext FileId', l_LastUsedExtFileId);  p('LastUsed Ext BlockId', l_LastUsedExtBlockId);  p('LastUsed Block', l_LAST_USED_BLOCK);   /*IFthe segment is analyzed */  IFp_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('25% -- 50% free spaceblocks', l_fs2_blocks);   p('25% -- 50% free spacebytes', l_fs2_bytes);    p('50% -- 75% free spaceblocks',l_fs3_blocks);   p('50% -- 75% free spacebytes', l_fs3_bytes);   p('75% -- 100% free spaceblocks', l_fs4_blocks);   p('75% -- 100% free spacebytes', l_fs4_bytes);   p('Unused Blocks', l_unformatted_blocks);    p('UnusedBytes', l_unformatted_bytes);   p('Total Blocks', l_full_blocks);   p('Total bytes', l_full_bytes);  END IF;END;

Usage:

_sys@FAKE> execdexter.show_space('BASICC3','T','AUTO','Y') ;Total Blocks............................384Total Bytes.............................3145728Unused Blocks...........................18Unused Bytes............................147456Last Used Ext FileId....................4Last Used Ext BlockId...................584704Last Used Block.........................110Thesegment is analyzed0% -- 25% free spaceblocks..............00% -- 25% free spacebytes...............025% -- 50% free spaceblocks.............025% -- 50% free spacebytes..............050% -- 75% free spaceblocks.............050% -- 75% free spacebytes..............075% -- 100% free spaceblocks............4375% -- 100% free spacebytes.............352256Unused Blocks...........................0Unused Bytes............................0Total Blocks............................309Total bytes.............................2531328 PL/SQL procedure successfully completed. _sys@FAKE> exec dexter.show_space('BASICC2');Total Blocks............................384Total Bytes.............................3145728Unused Blocks...........................54Unused Bytes............................442368Last Used Ext FileId....................4Last Used Ext BlockId...................584320Last Used Block.........................74

Script:

select table_name, compress_for, compression,PCT_FREE, PCT_USED  fromuser_tables wheretable_name in ('BASICC', 'BASICC2');

Note: When creating a compress table, specify the pct_free value. Otherwise, the results are not rigorous. In addition, you can use it with confidence during testing.

create table basicc2 tablespace users compressas select * from dba_objects ;

Create table as select to create a compressed table because it uses direct pathwrite/read.

Attachment 2 ACO White Paper

Aco White Paper:

Http://download.csdn.net/detail/renfengjun/7514403