Table compression in Oracle (11gR2)

The compression methods and features provided by oracle improve oracle's total compression methods by applying to different scenarios. They are: basiccompression: high compression, the lowest CPU overhead

The compression methods and features provided by oracle improve oracle's total compression methods by applying to different scenarios. Basic compression: high compression and lowest CPU overhead

Benefits of Compression

Compression is an Oracle feature that is more suitable for Data Warehouse Environments. The benefits of compression include:

1. It saves storage space and makes sense for massive data volumes.

2. the query performance will be improved (not absolutely) Because physical I/O is reduced and the hit rate of data blocks in the memory is increased.

Disadvantages:

It may increase the CPU load. Affects the performance of DML operations. Table shards increase, wasting storage space. For more details, refer:

These results are mainly due to the misuse of the table compression technology, because the table compression technology is an oracle feature that is more suitable for the data warehouse environment. That is, data is mainly used for query purposes and seldom involves DML operations.

Compression methods and features provided by oracle

Oracle improves the total compression mode by 4, which is applicable to different scenarios. They are:

Basic compression: high compression, the lowest CPU overhead, applicable to DDS.

OLTP compression: high compression, the lowest CPU overhead, applicable to OLTP and DDS.

Note: compression can be specified at the tablespace level, table level, and partition level. If the tablespace has the compression attribute, the tables in the table also have the compression attribute by default, but the table-level compression attribute can overwrite the table-level compression attribute, this is also the relationship between the compression attribute of the table and the compression attribute of the partition. The following describes table compression.

There are two other compression methods, but these two compression methods are limited in scope:

SQL> create table object_copy compress for query

2 as select * from dba_objects;

As select * from dba_objects

*

ERROR at line 2:

ORA-64307: hybrid columnar compression is only supported in tablespaces

Residing on Exadata storage

That is to say, to use a hybrid columnar compressed tablespace, it must be located on the exadata all-in-one machine.