Database Reference
In-Depth Information
Basic table compression was the first compression method to be introduced by Oracle. To use the method, it's
necessary to perform the loads through the direct-path interface. In other words, basic table compression compresses
data blocks only when one of the following operations is used:
CREATE TABLE ... COMPRESS ... AS SELECT ...
ALTER TABLE ... MOVE COMPRESS
INSERT /*+ append */ INTO ... SELECT ...
INSERT /*+ parallel(...) */ INTO ... SELECT ...
Loads performed by applications using the OCI direct-path interface (for example, the
SQL*Loader utility)
To make sure that as much data as possible is stored in every block, with basic table compression, the database
engine sets PCTFREE to 0 by default. In case data is inserted through regular INSERT statements, it's stored in
uncompressed blocks. Another disadvantage of basic table compression is that not only do UPDATE statements
commonly lead to migrated rows stored in uncompressed blocks, but also the free space in compressed blocks
caused by DELETE statements is usually not reused. For these reasons, I suggest using basic table compression only on
segments that are (mostly) read-only. For example, in a partitioned table storing a long history in which only the last
few partitions are modified, it could be useful to compress the partitions that are (mostly) read-only. Data marts and
completely refreshed materialized views are also good candidates for basic table compression.
Advanced row compression was introduced to support tables experiencing regular INSERT statements and also
modifications (such as from UPDATE and DELETE statements) by providing a compression ratio similar to basic table
compression (the internal storage is basically the same). Since the way this compression method works is dynamic
(data compression doesn't take place for every INSERT statement or modification; instead, it takes place when a given
block contains enough uncompressed data), it's difficult to give advice about its utilization. There are still several
situations in which advanced row compression isn't better than basic table compression. In addition, with advanced
row compression, modifications can generate much more undo and redo than for a uncompressed table. As a result,
to figure out whether advanced row compression is able to correctly handle data that is not (mostly) read-only,
I strongly advise you to carefully test first with the expected load.
Hybrid columnar compression is based on completely different technology. The key difference is that the
columns of a specific row are no longer stored sequentially, as shown in Figure 16-1 . Instead, data is stored column
by column and, as a result, columns of the same rows can be stored in different blocks. In addition, to cluster together
as much data of the same type as possible, the basic storage structure, called a logical compression unit , is composed
of several blocks. The aim of storing data column by column and of using larger storage structures is to achieve
much higher compression ratios. However, when processing compressed data, higher compression ratios are usually
related to higher CPU consumption. For that reason, you're able to choose between four compression levels (here
sorted according to the expected compression ratio and CPU consumption): QUERY LOW , QUERY HIGH , ARCHIVE LOW ,
and ARCHIVE HIGH . Note that on an Exadata system, while the decompression can be offloaded (a smart scan is
required, though), the compression is always performed by a database instance. Other drawbacks of hybrid columnar
compression:
Data is compressed only when loaded through the direct-path interface (same requirement as
for basic table compression); regular INSERT statements store data in blocks using advanced
row compression.
Row-level locking isn't supported; only whole logical compression units can be locked.
UPDATE statements lead
Even though row movement isn't explicitly enabled at the table level,
to row movement, and therefore, rowids can change.
In summary, I advise using hybrid columnar compression only in the same circumstances as basic table
 
Search WWH ::




Custom Search