How to compress Oracle massive data

"Data compression" used to be a new word for me, not have not heard, but did not actually use, has been doing project manager work is also designed to the database operations, but because the storage design is more abundant, in addition to the performance of the operation can allow customers to accept, so the compression technology is basically not how to use, It was also feared to have a negative impact on DML operations! The reason we have to experiment with this technology is because we have a very sharp increase in the amount of data in the second phase on the performance of the machine to the operation of a certain amount of pressure and impact, which also shows that the technology is in a specific environment, specific occasions down the use of the following, we will elaborate on how to use this technology---massive data of the weapon " Compress Compress "

First, the application of the occasion

A: As mentioned above, compression technology is generally used in the "mass data" category, in large data and high repetition rate of the situation should be good, suitable for OLAP (report system) is not suitable for OLTP (online trading system), when the amount of data hours is not very large, It makes sense to use this technique as long as the amount of data reaches a certain level.

Second, the advantages of compression technology

1. Save space: I think the biggest advantage of compression technology is to save space, in storage equipment space is very tight in the case of the need to use compression technology to alleviate

2. Reduce Memory/io overhead: When compression technology is used, the equivalent data blocks are relatively small, and the natural IO overhead is smaller.

3. Improve the efficiency of data processing: The original 10 blocks of data, after compression into 5 blocks, but still those data, efficiency increased by one times.

Third, the principle of compression

A: We have all used RAR compressed zip compression, in fact, the principles of these compression are very similar, of course, there are differences in the fine processing, the data block compression principle is to use shorter length symbol to replace each value in the column, because the symbol occupies less than the actual value of space, so as to achieve space-saving effect, The more a duplicate value in a column, the better the compression effect.

Iv. data compression is not suitable for OLTP systems

1.OLTP is a multi-transaction short time slice system with a large number of DML operations, while DML operations are known to have lock locks (insert update Delete) when the number of records in a block of data is more, the user who accesses the block is more likely to generate contention and wait, Thus reducing the efficiency.

2. Data inserts, updates, and index key value updates, the first to extract and update, the waiting time will be longer.

V. Compression of partitioned Tables

1. Each partition of the partition table can be compressed independently

2. In some cases, the partition table does not set the partition compression properties when it is created, and when a partition needs to be compressed, it is compressed independently.

3. If you set the compression properties at the table level, then all partitions will start the compression function, anyway, if it is a certain number of partitions independent compression, table-level properties do not write anything.

Experiment

CREATE TABLE Leo_partition1 (object_id,object_name) creates a partitioned table of six partitions without specifying a compression

Partition by range (OBJECT_ID)

(

Partition LEO_P1 values less than (1000),

Partition LEO_P2 values less than (2000),

Partition LEO_P3 values less than (3000),

Partition LEO_P4 values less than (4000),

Partition LEO_P5 values less than (5000),

Partition Leo_max values less than (MaxValue)

)

As select Object_id,object_name from Dba_objects;

Ls@leo> Select COUNT (*) from Leo_partition1;

COUNT (*)

----------

10351

Ls@leo> Select Partition_name,compression from user_tab_partitions where table_name= ' leo_partition1 ';

Partition_name COMPRESS

------------------------------ --------

Leo_max DISABLED

LEO_P1 DISABLED

LEO_P2 DISABLED

LEO_P3 DISABLED

LEO_P4 DISABLED

LEO_P5 DISABLED

ls@leo> ALTER TABLE Leo_partition1 compress; For example, to define a table-level compression property, where all partitions are compressed

Ls@leo> ALTER TABLE Leo_partition1 modify partition Leo_p1 compress; We test the compression of the LEO_P1 and LEO_P5 partitions to show that partitions can be compressed independently

Table altered.

Ls@leo> ALTER TABLE Leo_partition1 modify partition LEO_P5 compress;

Table altered.

Ls@leo> Select Partition_name,compression from user_tab_partitions where table_name= ' leo_partition1 ';

Partition_name COMPRESS

------------------------------ -----------------------

Leo_max DISABLED

LEO_P1 ENABLED