In this blog we are going to discuss the various types of database data compression, the differences between the compression types and when to use (and not use) the different data compression types.
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. Basic Compression compresses data that is loaded using bulk load operations (direct path), but does not compress data that is added/updated through conventional path DML operations (INSERT or UPDATE). If 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
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 path DML such as INSERT and UPDATE.
Advanced Row Compression (and Basic Compression) use 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 depends on the data being compressed, specifically the cardinality of the data. In general, organizations can expect to reduce their storage space consumption by a factor of 2x to 4x by using Advanced Row Compression and/or Basic Compression. 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 (HCC)
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 expect to reduce their storage space consumption by a factor of 6x to 15x+ by 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. While HCC compressed data can be modified using conventional path Data Manipulation Language (DML) operations, such as UPDATE and INSERT, HCC is best suited for applications with no, or very limited DML operations. If frequent UPDATE and INSERT operations are planned on a table or partition, then Advanced Row Compression (a feature of Oracle Advanced Compression) is better suited for such data. Hybrid Columnar Compression automatically compresses new data from SQL INSERT ... SELECT statements, without the APPEND hint and array inserts from programmatic interfaces such as PL/SQL and the Oracle Call Interface (OCI).
Source: oracle.com
Posts
0 comments:
Post a Comment