Technical Analysis of Oracle Data Compression

This article will explain Oracle Data Compression in two aspects: Using index keys to compress tables that are read-only or primarily read tables.

Oracle supports the compression of index structures and database tables. The compression algorithms of the two segments are similar in terms of extracting duplicate information and storing the duplicate information on the block once instead of storing the duplicate information each time.

The compression of indexes and tables is slightly different and can be used in different situations. For example, compression indexes can be used for tables that are frequently modified, but table compression does not make any sense in this case.

1. Oracle Data Compression Using index key Compression

Compression of the index key in Oracle allows compression of the duplicate part before the index key, and each leaf block instead of each leaf block stores the duplicate part value once per line.

Advantages:

The compressed index occupies less disk space, thus saving storage.

Compressing indexes reduces the amount of physical I/O of the system.

Compressing indexes improves the cache efficiency of the buffer zone. You only need to cache less blocks. Index blocks are both compressed and saved in high-speed cache.

Disadvantages:

Compress the index and place more row items each, increasing the competition for these compact data structures. If each leaf block was previously placed with 200 rows, about 400 rows are now placed.

Compress indexes at run time requires a little CPU time for processing, because this structure is more complex. You may see this in the insert and select operations.

2. Oracle Data Compression: Table compression for read-only or read-only tables

The main difference between table compression and index compression lies in the use. The index key compression is equivalent to the regular modification, read-only, or read-only system of the table. Regardless of the environment, indexes are maintained in the compressed state. However, table compression only works in read-only or read-only environments.

Table compression may be useful in the following scenarios:

A large number of read-only or mostly read static reference information

Data Warehouse Environments with batch operations are often available.

Audit Trail information stored in the partition table. The audit information of the previous month can be compressed starting from the new month.

Oracle Data Compression summary:

Compression is a powerful tool to save disk space, but it is a compromise with other storage considerations. If the data location is critical, the data must be clustered to provide the best performance. Compression may not be the best method. On the other hand, if you need to store a large amount of read-only data, such as audit tracking information and data warehouse tables, compression may be the tool you need.

In terms of performance, the compressed table is quite good. Similar to the cost of index key compression, the overhead of data block decompression is also small. In addition, the overhead is far exceeded due to the benefits such as reducing I/O, making more efficient use of the buffer cache and reducing the plug-in lock.

The above is an overview of Oracle data compression.