Implementation methods and features of table compression in Oracle database

Implementation methods and features of table compression in Oracle database

1 Basic Table Compression

Basic table compression is mainly used for less updated tables, such as data warehouses. It reduces storage space by compressing data inserted by direct path loading. This compression method supports limited data types and SQL operations.

CREATE TABLE my_table (
    id NUMBER,
    large_text CLOB
) COMPRESS BASIC;

Features：

• High compression rate.
• Applicable only for data loaded directly by paths.
• Supports limited data types and SQL operations.

2 OLTP Table Compression

OLTP table compression is suitable for OLTP applications and is able to compress data from any SQL operation. This compression method uses the row-main format when storing compressed rows, and all columns are stored together, thereby improving read performance.

CREATE TABLE my_oltp_table (
    id NUMBER,
    large_text CLOB
) COMPRESS FOR OLTP;

Features：

• Suitable for OLTP applications.
• Compresses data for any SQL operation.
• Improve read performance.

3 Table compression implementation strategies

To avoid business impact, Oracle recommends that new written data be compressed without compressing it, but for more than half a year or a year. When compressing data, in order to reduce the pressure of free space and undo space, it is recommended to compress individual partitions one by one.

ALTER TABLE my_table
  MOVE PARTITION p1 COMPRESS FOR OLTP;

Implementation strategies：

• No compression is performed on newly written data.
• Compress data for more than half a year or a year.
• Compress individual partitions one by one, using the online redefinition function.

4 Benefits of table compression

Table compression not only saves storage space, but also improves database performance. Oracle can directly read compressed data blocks, reducing I/O, and thus improving performance. In addition, compressed data blocks can make more efficient use of buffer cache.

benefit：

• Save storage space.
• Reduce memory usage.
• Improve query execution speed.
• Improve the efficiency of buffer cache.

Table compression technology in Oracle databases provides a variety of options to suit different application scenarios and needs. By placing and using these compression technologies rationally, storage space can be significantly optimized and database performance can be improved.

This is the article about the implementation methods and features of table compression in Oracle database. For more related Oracle table compression content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!