Summary of Oracle compression features introduction to the compression function

Compression capabilities for Oracle databases

Everyone heard this word, must have thought of saving hard disk space it. Yes, it is used to do this, but to extend it, it can not only save hard disk space, but also save bandwidth and memory usage (CPU has a little bit of loss because of the need for more compute value operation, but because most of the environment the CPU is idle, so it can be ignored).

Basiccompression This feature is available from 9i onwards, 11GR1 begins to provide new compression capabilities, i.e. OLTP compression. Basic Compression is included in the EE version and does not require additional charges, but OLTP Compression requires additional Oracle Advanced Compression Optionlicense. Senior? Want to use? Pay for it.

The following image can help you understand the principle of the compression block in a nutshell:

Simply put, there is a special data structure in the block,symbol table.

The contents of the symboltable dump are as follows:

tab 0, row 0, @0x1da4tl:7 fb:--h-fl--lb:0x0  cc:12col  0:*null*col  1: [5]  4c 44col  2: [1]< C6/>4ecol  3:*null*col  4: [1]  4ecol  5: [1]  4ecol  6: [3]  53col  7: [7]  6f 0a 1e 0d 9 19col  8:[19]  [2] 2d/2d + 3a 3a 3132 3a  05/C1 Col: [5] 4e 58col: [7] 6f 0a 1e 0d 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]  4c 44col  2: [1]< C5/>4ecol  3:*null*col  4: [1]  4ecol  5: [1]  4ecol  6: [3]  53col  7: [7]  6f 0a 1e 0d 9 19col  8:[19]  [2] 2d/2d + 3a 3a 3132 3a  05/C1 Col: [5] 4e 58col: [7] 6f 0a 1e 0d 19bindmp:00 2a 0c 15 20 24 22

A bit similar to the clustertable block structure. Simply put the contents of the block duplication into a symbol table , the real table row piece References the duplicate content that is stored in the symbol table . because both Basiccompression and oltpcompresssion are block-compressed, even if they have duplicate content, they cannot be compressed if they are not stored in the same block. The principle of the first introduction to this, if you want to further research, you can refer to the old Bear blog.

Oracle compressed table Data block Format parsing:

Http://www.laoxiong.net/dissect_compressed_block_part1.html

The concept is finished, the following is a brief introduction to table compression of several types:

1. Do not compress

Don't be impatient, I think it should be considered one of the types of table compression, in order to be rigorous, here it is ranked first. Keyword nocompress. It means not compressing. By default, when you create a table, you determine whether compression is based on the default property of your table space. If your tablespace is defined as default compress, you will need to specify nocompress when creating a non-compressed table in this table space. Example:

CREATE TABLE T (ID number) nocompress;

Related SQL (for everyone to test 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 nocom Press; Select Tablespace_name, compress_for fromdba_tablespaces where tablespace_name= ' USERS ';

2. Basic compression

L point 1: Data compression only takes effect when directpath load, and no compression occurs for normal DML statement inserts or update.

L Point 2: When compressing to ensure maximum compression, the Pctfree is automatically set to 0 (not mandatory, or you can set Pctfree to other values when building the table).

Example:

CREATE TABLE T (ID number) compress; CREATE table T (ID number) compress basic;

3. OLTP compression

L Point 1: Unlike basic compression,oltpcompression, which takes effect on all DML, it is suitable for OLTP systems.

L Key 2: Set Pctfree to 10 automatically, or you can set it yourself.

L important 3:oracle the compression action within the database is not generated in all DML operations, but only when the data in the new block reaches the value of the block.

Here's a three-point description:

Oracle The compression operation is caused by the database compressed data block in batch mode , not every action that changes the data. The Block's compression operations are triggered when the uncompressed data in the block reaches a value.

When a transaction causes the data in the block to reach its value, the contents of all blocks are compressed. Then, more data is added to the block, once again reaching the value, the entire block will be re-compressed to achieve the maximum level of compression. This process repeats until the Oracle database determines that no more benefits can be gained from compression. Only certain transactions that trigger the compression condition experience a slight compression overhead.

So most OLTP transactions work on a compressed block, and have the same performance on an uncompressed table. Only those unlucky operations will trigger the compression action within the block.

You can refer to the ACO White paper for more information here.

Example:

CREATE TABLE T (ID number) compress for OLTP;

4. HCC (Hybrid columnar Compression)

This feature is available only for Oracle databases in the Exadata all-in-one environment. Can be divided into 4 levels, namely:

L Query Low

L Query High

L Archive Low

L Archive High

Comparisons apply to tables that change infrequently or readonly. According to the level of the above arrangement, the corresponding compression ratio is more and more high, see the introduction, unexpectedly up to 30 times times, it is really staggering (of course, this should be the best case).

The principle of simple description:

Unlike traditional data stores, all the information in a row is stored together. Instead, the structure of the CU(compressionunit) is used to store the data, storing the information for each column in a group (many rows) of rows, and then performing the compression operation. Can save more space. (It's just a block reference, it's now a multi-block reference, and it certainly saves more space) but again, as a result of this storage structure, all rows in the logical Compression unit will be locked together in the event of updating 1 rows of data.

I am not oracledatabase sales, so the contents of HCC are introduced here. Finally, let's talk about some of the limitations of compression:

1. Compress Basic and Compress FOROLTP do not support more than 255 columns of information. (Does it really hurt because more than 255 rows of row will cross multiple row piece?) ）

2. Basicfile LOBs does not support compression, Securefilelobs supports compression. (New things bring new bugs)

3. Compress basic cannot be dropcolumn, but it is allowed for the various compression formats in compress for OLTP and HCC.

4. Index organization table is not supported.

5. External tables or clustertable are not supported (just say support for stacked tables only).

The limitation content of HCC is not described.

Attachment

Attachment 1 Show_space Script

The following is a show_space (itpub modified) script:

CREATE OR REPLACE PROCEDURE show_space (p_segname_1 in VARCHAR2, p_type_1 in VA                                      RCHAR2 default ' TABLE ', p_space in VARCHAR2 default ' AUTO ', p_analyzed in VARCHAR2 DEFAULT ' N ', p_partition_1 in VARCHAR2 Defau LT NULL, p_owner_1 in VARCHAR2 defaultuser) Authidcurrent_user as P_segname VA RCHAR2 (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_block                          s = l_total_blocks, total_bytes = L_total_bytes, Unused_blocks = l_unused_blocks, unused_bytes = L_un Used_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_b Ytes, 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_block   s = = 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;

How to use:

[email protected]> 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. [email protected]> 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

Small script:

Select table_name, Compress_for, Compression,pct_free, pct_used  fromuser_tables wheretable_name in (' BASICC ', ' BASICC2 ');

Note that you specify the value of Pct_free when creating compress compressed tables, otherwise the results are not rigorous. In addition to doing the test, you can rest assured that the use

CREATE TABLE BASICC2 tablespace users compressas select * from Dba_objects;

CREATE TABLE as Select creates a compressed table, because it uses direct Pathwrite/readitself.

Annex 2 ACO White Paper

ACO White Paper:

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