Free compression, yes, you heard me correctly, free compression. In this blog we will look at two compression features included with Oracle Database Enterprise Edition, these include Basic Table Compression and Prefix Key Compression.
Basic Table Compression
If you’re not familiar with Basic Table Compression, then some important points to know about Basic Table Compression is that more than a decade ago, Oracle Database 9i Release 2 introduced Basic Table Compression, which compresses data that is loaded using bulk load operations, but doesn’t compress data that is added/changed through conventional DML operations (INSERT or UPDATE) – if INSERTS and UPDATES are performed on a Basic compressed table over time, then that table would have to be re-compressed to get the changes compressed.
Basic Table Compression works by eliminating duplicate values within a database block, even across multiple columns. Compressed blocks contain a structure, called a symbol table, which 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.
It should be noted that the compression ratio achieved 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 Basic Table Compression. That is, the amount of space consumed by uncompressed data will be two to four times larger than that of the compressed data.
Prefix Key Compression
Prefix Key Compression is perhaps one of the oldest compression features within the Oracle database, released with Oracle 8.1.3 (even before Basic Table Compression in 9.2). If used correctly, Prefix Key Compression has the potential to substantially reduce the overall size of indexes. It helps both multi-column unique indexes and non-unique indexes alike and is also one of the most critical index optimization options available.
Prefix Key Compression compresses portions of the key values in an index segment (or Index Organized Table), by reducing the storage inefficiencies of storing repeating values. It compresses the data by splitting the index key into two parts; the leading group of columns, called the prefix entry (which are potentially shared across multiple key values), and the suffix columns (which is unique to every index key). As the prefixes are potentially shared across multiple keys in a block, these can be stored more optimally (only once) and shared across multiple suffix entries, resulting in the index data being compressing.
Prefix Key compression is done in the leaf blocks of a B-Tree index. The keys are compressed locally within an index leaf block, that is, both the prefix and suffix entries are stored within same block. Suffix entries form the compressed representation of the index key. Each one of these compressed rows refers to the corresponding prefix, which is stored in the same block. By storing the prefixes and suffixes locally in the same block, each index block is self-contained and in order to construct the complete key there is no additional block IO involved.
Use Cases
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. OLTP Table Compression can be useful for reducing the storage requirements of read-only/read-mostly data.
Indexes often take up to 50% of the total database space and it is not uncommon to have 10-20 indexes on a single table (in extreme cases, I have seen upward of 100 indexes per table). Prefix Key Compression can be useful in reducing index storage requirements for both OLTP and data warehouse applications.
Benefits of Compression
Database compression enables organizations to better utilize existing database storage, and possibly defer additional purchases of new storage, for growing applications. Because Basic Table Compression and Prefix Key Compression can help reduce the total data size in the application database, storage requirements grow much slower than non-compressed databases.
Fortunately, the benefits of compression go beyond just on-disk storage savings. Another significant advantage is Oracle Database’s ability to read/process compressed data (and indexes) directly, in memory, without uncompressing the data. This helps improve query performance due to the reduction in I/O, and the reduction in system calls related to the I/O operations. Further, the database’s buffer cache becomes more efficient by storing more data without having to add memory.
More Information