Database Reference
In-Depth Information
To take advantage of data compression for full scan operations, as shown in the previous example (in other
words, to make full scan operations faster), it might be necessary to have spare CPU resources. This isn't because of
the CPU overhead of “uncompressing” the blocks (which is very small because the default compression is based on a
fairly simple algorithm that only deduplicates repeated column values) but simply because the operations performed
by the SQL engine (in the previous example, accessing the blocks and doing the count) are executed in a shorter
period of time. Also note that reducing the number of physical I/O operations also reduces CPU consumption. For
example, on my test system, the average CPU utilization at the session level during the execution of the test queries
was about 18% without compression and 27% with compression.
Requirements
Oracle Database Enterprise Edition (and not any other edition) provides several data compression methods. In
addition, some of them are available only in specific releases; others are limited by licensing issues. Table
16-2
summarizes which release provides which method, and the licensing requirements to use them.
Table 16-2.
Compression Methods Provided by Oracle Database
Method
Versions
Licensing Requirements
Basic table compression
From 9.2 onward
None.
Advanced row compression
(a.k.a. OLTP table compression)
From 11.1 onward
Advanced Compression option.
Hybrid columnar compression
From 11.2 onward
The tablespace containing the data must reside in either
Exadata storage, ZFS storage, or Pillar Axiom 600 storage.
Whether a data compression method can be applied also depends on the implementation of the table to be
compressed. Specifically, the following limitations apply:
•
Only heap tables can be compressed (index-organized tables, external tables, and tables that
are part of a cluster aren't supported).
•
Except for hybrid columnar compression, the compressed table can't have more than 255
columns.
LONG
or
LONG RAW
columns.
•
The compressed table can't have
•
The compressed table can't have row-level dependency tracking enabled.
sys
user or be stored in the
system
tablespace.
•
The compressed table can't be owned by the
Methods
To give an overview of the differences between the three methods mentioned in Table
16-2
, let's quickly look at their
key characteristics and when they should be implemented.
