Basic Table Compression
If you are not familiar with Basic Table Compression, then some important points to know about Basic Table Compression are that it is a free data compression capability and it is included with Oracle Database Enterprise Edition. Oracle Database 9i Release 2 introduced Basic Table Compression, which compresses data that is loaded using bulk load operations, but does not compress data that is added/changed through conventional DML operations (INSERT or UPDATE). If DML INSERTS and UPDATES are performed on a Basic compressed table/partition over time, then that table/partition would have to be re-compressed to get the changes compressed.
USAGE: Basic Table Compression isn’t intended for OLTP applications, and instead, is best suited for data warehouse applications (read-mostly) where data is loaded using bulk load operations and is never (or very rarely) modified.
Advanced Row Compression
Oracle Database 11g Release 1 introduced OLTP Table Compression, now called Advanced Row Compression with Oracle Database 12c and above. Advanced Row Compression is the data compression feature of Advanced Compression that uses the same algorithm as Basic Compression, but differs from Basic Compression in that Advanced Row Compression maintains data compression during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE.
Advanced Row Compression uses a compression algorithm specifically designed to eliminate duplicate values within a database block, even across multiple columns. The compression ratio achieved in a given environment (also true for Basic Compression) depends on the data being compressed, specifically the cardinality of the data.
In general, organizations typically see a compression ratio in the range of 2x to 4x when using Advanced Row Compression (Basic compression produces similar compression ratios). That is, the amount of space consumed by uncompressed data will be two to four times larger than that of the compressed data.
USAGE: Advanced Row Compression is intended for both OLTP and Data Warehouse applications.
Hybrid Columnar Compression
Unlike both Basic and Advanced Row Compression, Oracle’s Hybrid Columnar Compression technology utilizes a combination of both row and columnar methods for storing data. This hybrid approach achieves the compression benefits of columnar storage, while avoiding the performance shortfalls of a pure columnar format.
A logical construct called the compression unit (CU) is used to store a set of hybrid columnar compressed rows. When data is loaded, column values for a set of rows are grouped together and compressed. After the column data for a set of rows has been compressed, it is stored in a compression unit. To maximize storage savings with Hybrid Columnar Compression, data must be loaded using bulk loading (direct path) techniques.
Examples of bulk load operations commonly used includes: Insert statements with the APPEND hint, Parallel DML, Direct Path SQL*LDR and/or Create Table as Select (CTAS). In general, organizations can typically expect compression ratios in the range of 6x to 15x when using Hybrid Columnar Compression.
USAGE: Hybrid Columnar Compression is best suited for data warehouse applications (read-mostly) where data is loaded using bulk load operations and is never (or very rarely) modified.
Posted from:https://blogs.oracle.com/dbstorage/post/when-to-use-the-various-types-of-oracle-data-compression
- Advanced Row Compression
- Advanced Index Compression
- RMAN Backup Compression
- Advanced LOB Compression/Deduplication
- Data Guard Redo Transport Compression
Advanced Row Compression
Oracle Database 11g Release 1 introduced OLTP Table Compression, now called
Advanced Row Compression, which maintains compression during all types of
data manipulation operations, including conventional DML such as INSERT and
UPDATE. In addition, Advanced Row Compression minimizes the overhead of
write operations on compressed data, making it suitable for transactional / OLTP
environments as well as data warehouses, extending the benefits of
compression to all application workloads.
Advanced Row Compression uses a unique compression algorithm specifically
designed to work with OLTP/DW applications. The algorithm works by
eliminating duplicate values within a database block, even across multiple
columns. Compressed blocks contain a structure called a symbol table that
maintains compression metadata. When a block is compressed, duplicate values
are eliminated by first adding a single copy of the duplicate value to the symbol
table. Each duplicate value is then replaced by a short reference to the
appropriate entry in the symbol table.
Enabling Advanced Row Compression
For new tables and partitions, enabling Advanced Row Compression is easy:
simply CREATE the table or partition and specify “ROW STORE COMPRESS
ADVANCED”. See the example below:
CREATE TABLE emp (emp_id NUMBER, first_name
VARCHAR2(128), last_name VARCHAR2(128)) ROW STORE
COMPRESS ADVANCED;
Advanced Index Compression
Advanced Index Compression, part of Advanced Compression with Oracle
Database, helps automate index compression so that a DBA is no longer required
to specify the number of prefix columns to consider for compression (as is
required with Index Key Compression).
Advanced Index Compression is an enabling technology for multiple
compression levels – LOW and HIGH. Average compression ratios can range
from 2x to 5x depending on which compression level is implemented. With
substantial storage savings from Advanced Index Compression, IT managers can
drastically reduce and often reduce the need to purchase new storage.
Enabling Advanced Index Compression:
Advanced Index Compression can be enabled by specifying the COMPRESS
ADVANCED sub-clause of the CREATE/ALTER INDEX clause. New indexes can
be automatically created as compressed, or existing indexes can be rebuilt
compressed.
CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS ADVANCED
LOW/HIGH;
You want to use table compression suitable for OLTP that will:
1. Compress rows for all DML statements on that table
2. Minimize the overheads associated with compression
Which compression option is best suited for this?
- A. COLUMN STORE COMPRESS FOR QUERY LOW
- B. ROW STORE COMPRESS BASIC
- C. COLUMN STORE COMPRESS FOR ARCHIVE LOW
- D. COLUMN STORE COMPRESS FOR ARCHIVE HIGH
- E. ROW STORE COMPRESS ADVANCED