Oracle table compression technology

Oracle compression technology includes basic table compression, OLTP table compression, and index compression) and hybrid column compression (hybrid columnar compression (HCC )).

Basic compression was launched from 9i and is the default compression method of oracle. OLTP compression is launched at the beginning of 11g and supports data compression for all types of DML operations. Compression can save disk space, but may increase CPU resource consumption. This article mainly discusses common basic and LTOP compression, index compression, and HSCC. For more information, see other oracle documents. Table compression technology is suitable for historical tables with little data changes in OLAP and OLTP systems. It is not suitable for tables with frequent DML operations.

1.1 compression principle

Taking OLTP compression as an example, refer to the instructions in document 4. The principles are as follows:

Please refer to an ACCOUNTS table, which contains the following records:

In the database, assume that a database block contains all the above rows.

The decompressed block looks like this: all fields (columns) in the record contain data. When this block is compressed, the database first calculates the duplicate values found in all rows, moves these values out of the route, and places them near the block header. The duplicate values in the row are replaced with a symbol that represents each value. Conceptually, it looks as shown in. You can see the blocks before and after compression.

Note how these values are extracted from the row and placed in a special area on the top called the symbol table. Each value in the column is assigned a symbol, which replaces the actual value in the row. Because the space occupied by the symbol is smaller than the actual value, the record size is far smaller than the initial value. The more duplicate data in the row, the more compact the symbol table and block.

Because compression occurs as a trigger event rather than during insertion, compression in normal DML processes has no effect on performance. After the compression is triggered, the CPU requirement will certainly become high, but the CPU impact is zero at any other time. Therefore, compression is also applicable to OLTP applications, this is the balance point in Oracle Database 11g compression.

In addition to reducing space usage, compressing data also shortens network transmission time, reduces backup space, and makes it practical to maintain a complete copy of the production database in QA and testing.

1.2 basic Compression

The following experiment shows the effects of basic compression and OLTP compression and their similarities.

Six groups of experiments in basic compression to compare table compression in various situations

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36 38 39 40 41

Sys @ MS4ADB3 (dtydb5)> select count (*) from test; COUNT (*) ---------- 50000 -- 1. baseline CTAS create table t1 tablespace users as select * from test where rownum <= 50000; -- 2. CTAS with basic compression enabled create table t2 compress basic tablespaceusers as select * from test where rownum <= 50000; -- 3. normal insert into empty table defined as compressed create table t3 compress basic tablespaceusers as select * from test where rownum = 0; insert into t3 select * from test whererownum <= 50000; -- 4. direct path insert into empty table defined as compressed create table t4 compress basic tablespaceusers as select * from test where rownum = 0; insert/* + append */into t4 select * fromtest where rownum <= 50000 -- 5. CTAS without compression, then change to compressed create table t5 tablespace users as select * from test where rownum <= 50000; alter table t5 compress basic;

?

1 2 3 4 5 6

--- 6. table move compress create table t6 tablespace users as select * from test where rownum <= 50000; alter table t6 move compress basic;

Table Analysis

?

1 2 3 4 5 6 7 8 9 10 11

Values ('sys ', 'T1'); execdbms_stats.gather_table_stats ('sys ', 't2'); values ('sys ', 't3'); execdbms_stats.gather_table_stats ('sys ', 't4 '); execdbms_stats.gather_table_stats ('sys', 't5 '); execdbms_stats.gather_table_stats ('sys', 't6 ');

Query table space usage

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 26 27 28

Sys @ MS4ADB3 (dtydb5)> select table_name, blocks, pct_free, compression, compress_for 2 from user_tables 3 where table_name in ('t1', 't2', 't3 ', 't4 ', 't5', 't6 '); TABLE_NAME BLOCKS PCT_FREE COMPRESSION COMPRESS_FOR quota ---------- -------------- average T1 666 10 DISABLED T2 204 0 enabled basic T3 622 0 enabled basic T4 204 0 enabled basic T5 666 10 enabled basic T6 204 0 enabled basic sys @ MS4ADB3 (dtydb5)> selectsegment_name, bytes/1024 K from dba_segments where segment_name in ('t1', 't2', 't3 ', 't4', 't5 ', 't6 '); SEGMENT_NA K --------- ---------- T1 6144 T2 2048 T3 5120 T4 2048 T5 6144 T6 2048

Result Analysis:

As shown in the preceding figure,

Basic compression

In CATS, insert/* + append */and move compress operations compress data. The alter table compress operation modifies the compression attribute of the table, but does not compress existing data. The normal insert operation on the compressed table does not compress the data. The PCT_FREE value of the compressed table is 0, indicating that the purpose of oracle's basic compressed table design is to think that such tables will be rarely modified in the future.

1.3 OLTP Compression

Perform the following six groups of experiments with OLTP compression:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36

-- 1. baseline CTAS create table t21 tablespace users as select * from test where rownum <= 50000; -- 2. CTAS with OLTP compress enabled create table t22 compress for OLTP tablespace users as select * from test where rownum <= 50000; -- 3. normal insert into empty table defined as compressed create table t23 compress for OLTP tablespace users as select * from test where rownum = 0; insert into t23 select * from test where rownum <= 50000; -- 4. direct path insert into empty table defined as compressed create table t24 compress for OLTP tablespace users as select * from test where rownum = 0; insert/* + append */into t24 select * from test where rownum <= 50000; -- 5. CTAS without compression, then change to compressed create table t25 tablespace users as select * from test where rownum <= 50000; alter table t25 compress for OLTP; --- 6. table move compress create table t26 tablespace users as select * from test where rownum <= 50000; alter table t26 move compress for OLTP;

Table Analysis

?

1 2 3 4 5 6

Exec values ('sys ', 't21'); exec dbms_stats.gather_table_stats ('sys', 't22'); exec dbms_stats.gather_table_stats ('sys ', 't23 '); exec values ('sys ', 't24'); exec dbms_stats.gather_table_stats ('sys', 't25'); exec dbms_stats.gather_table_stats ('sys ', 't26 ');

Table space used

?

1 2 3 4 5 6 7 8 9 10 11 12

Sys @ MS4ADB3 (dtydb5)> select table_name, blocks, pct_free, compression, compress_for 2 from user_tables 3 where table_name in ('t21', 't22', 't23 ', 't24', 't25', 't26 '); TABLE_NAME BLOCKS PCT_FREE COMPRESSION COMPRESS_FOR quota ---------- quota T21 666 10 DISABLED T22 225 10 enabled oltp T23 370 10 enabled oltp T24 225 10 enabled oltp T25 666 10 enabled oltp T26 225 10 ENABLED OLTP

Comparison Analysis

OTLP compression is used to compress DML operations (T23 table). The principle is that when data is inserted into an empty block, the data is not compressed. Only when the data exceeds a threshold value, in this case, oracle compresses data blocks and may compress the same data block multiple times.

3 methods for converting to a compressed table

1. alter table... COMPRESS FOR OLTP

This method does not compress existing data and uses OLTP to compress data related to subsequent DML statements.

2. Online Redefinition (DBMS_REDEFINITION)

Compress existing and future data. You can use DBMS_REDEFINITION to operate tables online and parallel operations. The global index of a partitioned table is an exception. You need to recreate the index after online redefinition.

3. alter table... MOVE COMPRESS FOR OLTP

Compress existing and future data. During the move process, the lock (X) will be applied to the table, and the DML operation will be blocked. You can use parallel operations to improve performance. The move operation will cause the index to fail. Therefore, you need to re-create the index after moving. The move operation can change the tablespace of the segment.