Use Oracle 11g Data Compression Technology

As the amount of data is constantly increasing, the CPU is constantly strong, and the dual-core and quad-core features are constantly changing, a time-to-space optimization technology should become increasingly popular. Therefore, Data Compression should change from nuclear weapons to conventional weapons for future databases. Oracle has been introducing table-level compression since 9i. It has always been like a nuclear power plant in China. It is very useful for publicity, and the actual contribution is not very good.

Oracle11g seems to be the main tool to promote data Compression technology. It specifically launched a component called Advance Compression, which fully supports common table Compression and unstructured data Compression, SecureFile data Compression ), data Pump Data compression, and RMAN backup compression. The Data compression technology has become a stage of history. Since it was launched as an Option, Oracle must be very confident in this feature, so you need to separately purchase License for this feature.

Although Table compression is introduced in Oracle9i, there are many restrictions. Only data involved in batch loading operations, such as direct path loading and CTAS, can be compressed. Data in common DML operations cannot be compressed. This should not solve the compression problem of write operations. It has been left over to Oracle11g and finally solved the write performance problem of relational data compression. Oracle table compression is Block-level data compression. The main technology is similar to Oracle9i. You can also introduce the symbol table in the Block to represent repeated data in the block with an item. Oracle will batch compress blocks, instead of compressing each time data is written into the block. This method can minimize the impact of data compression on DML operations. In this way, a new parameter should be introduced at the block level to control the compression operation after the uncompressed data volume in the block reaches a certain standard.

SecureFile is also a new feature of Oracle11g, used to store unstructured data. SecureFile also supports data compression. In this way, the traditional LOB fields can also be compressed, which will greatly reduce the storage space requirements of large databases. Of course, there are gains and losses. When compression and decompression are performed, the CPU requirements will also be higher. However, at present, the CPU development speed is significantly faster than IO and storage space, compression is a promising technology. By constantly improving the compression ratio and compression efficiency, we should make it a standard configuration for various databases in the future.

In addition to compressing data in the database, Advance Compression Option also supports Compression of backup data. Data Pump for logical backup and RMAN for physical backup will support this technology. In Oracle10gR2, Data Pump has started to support source Data compression, while in Oracle11g, you can directly compress and export files, which can greatly reduce the storage space needs during export. In earlier versions, using WinRAR and so on, we often can compress several GB of exported files to dozens of MB. In the Oracle11g White Paper, we can say that the compression ratio can reach 74.67%, you can perform a good test. Similarly, Oracle also introduced RMAN compression technology in 10 Gb. However, Oracle11g claims to adopt more advanced ZLIB algorithms, which can be 40% faster than Oracle10g compression algorithms and reduce space requirements by 20%.

In addition to the above data Compression technology, Oracle 11g Advanced Compression Option will also introduce another Compression technology. We know that in Data Guard, logs need to be transferred from the master database to the slave database. If the master database has many transactions, the amount of logs to be transferred per unit time will be considerable. If the logs can be compressed and then transmitted, and then applied after the slave database is decompressed, the network bandwidth needs will be greatly reduced, thus reducing the time difference between the master and slave databases.

In addition, Oracle's bitmap has always been compressed for storage. bitmap in 10 Gb has made great changes to 9i. Through some details, it provides better performance and higher stability, it is also consistent oracle style. How bitmap is implemented in Oracle11g is also a feature worth noting.

From Oracle11g, nothing cannot be compressed. Using a more powerful CPU can reduce or delay the endless thirst for storage space. Perhaps many large OLTP and most data warehouses will benefit from data compression technology.

1. Effect of disk sorting on Oracle database performance (1)
2. Prospective role in Oracle Database Maintenance
3. How to check Oracle availability and tablespace capacity
4. Optimize Oracle tablespace design to improve database performance
5. Use Resource Manager to optimize Oracle Performance