As databases grow in size, so does the need for effective strategies to handle this growth. This is where data compression, including table compression, comes into play.
Data compression is a technique that reduces the storage space needed to save data, and in turn, optimizes the performance of database operations. It works by eliminating redundant data, thereby shrinking the size of the data without losing any information.
Table compression is a specific form of data compression in Oracle databases. It reduces the amount of storage used by table data and can significantly improve the performance of I/O-intensive operations. But it’s not just about saving space – table compression can also enhance query performance, backup speed, and data transfer rates.
Types of table compression
Oracle Database offers several types of table compression, each suited to different use cases:
- Basic Table Compression: Introduced in Oracle Database 9i Release 2, Basic Table Compression compresses data loaded using bulk load operations, but does not compress data added or updated through conventional DML operations. It’s best suited for data warehouse applications where data is loaded using bulk load operations and is rarely modified.
- Advanced Row Compression: Also known as OLTP Table Compression, Advanced Row Compression was introduced in Oracle Database 11g Release 1. It uses the same algorithm as Basic Compression, but maintains data compression during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. It’s intended for both OLTP and Data Warehouse applications.
- Hybrid Columnar Compression (HCC): Unlike both Basic and Advanced Row Compression, HCC 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.
In addition to these, Oracle also allows compression to be performed on several partitions or a complete partitioned heap-organized table. This can be done by either defining a complete partitioned table as being compressed, or by defining it on a per-partition level.
Remember, the choice of compression type depends on the specific requirements of your database and the nature of the operations you perform. It’s always a good idea to test different compression types with your data and workload to determine the most suitable option. Check out this Gregg Christman’s post for more info.
Impact of compression in Oracle Database performance.
Table compression does have an impact on Oracle Database performance, but the impact can be both positive and negative, and it largely depends on the specific use case.
Positive Impact:
- Reduced Storage Footprint: Compression reduces the overall database storage footprint for all types of data. This can lead to significant savings in storage costs.
- Improved Query Performance: Oracle Database can read and process compressed data directly in memory, without uncompressing the data. This can improve query performance due to the reduction in I/O operations and system calls related to I/O.
- Improved Performance for Large Data Access: Compression can have a significant positive impact on queries accessing large amounts of data, as well as on data management operations like backup and recovery.
Potential Negative Impact:
- Overhead of Compression and Decompression: While the compression process can save space, it also introduces additional CPU overhead for the compression and decompression operations. However, Oracle’s compression algorithms are designed to minimize this overhead.
- Dependency Issues: If not managed properly, compression can introduce issues with dependencies, particularly when dealing with objects like views or foreign keys that depend on other database objects.
It’s important to note that the impact of compression on performance is nearly impossible to predict precisely, as it depends on the data, the order the data is inserted into the table, the number of updates, and other factors. Therefore, it’s always a good idea to test different compression types with your data and workload to determine the most suitable option.
Hands-on.
As a proof of concept, let’s create the same table three times: the first one without using compression, the second one using basic compression and the last one using advanced compression. Later on, we’ll show the number of blocks used by each one of them.
SQL> create table test_nocompress as select * from dba_objects where rownum <= 10000;
Table created.
SQL> create table test_compress_basic COMPRESS BASIC as select * from dba_objects where rownum <= 10000;
Table created.
SQL> create table test_compress_advanced ROW STORE COMPRESS ADVANCED
as select * from dba_objects where rownum <= 10000;
Table created.
SQL> analyze table test_nocompress compute statistics;
Table analyzed.
SQL> analyze table test_compress_basic compute statistics;
Table analyzed.
SQL> analyze table test_compress_advanced compute statistics;
Table analyzed.
SQL> set linesize 500
SQL> column table_name format a30
SQL> select table_name, blocks, pct_free, compression, compress_for from user_tables where table_name like 'TEST%';
TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
------------------------------ ---------- ---------- -------- ------------------------------
TEST_COMPRESS_ADVANCED 43 10 ENABLED ADVANCED
TEST_COMPRESS_BASIC 39 0 ENABLED BASIC
TEST_NOCOMPRESS 173 10 DISABLED
SQL>
Now, let’s compress the first table created without compression. We’ll see that the existing rows are not compressed after the ALTER TABLE <tablename> COMPRESS BASIC statement. In fact, they will not be compressed until the ALTER TABLE <tablename> MOVE statement is invoked.
SQL> select table_name, blocks, pct_free, compression, compress_for from user_tables where table_name = 'TEST_NOCOMPRESS'; TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR ------------------------------ ---------- ---------- -------- ------------------------------ TEST_NOCOMPRESS 173 10 DISABLED SQL> alter table TEST_NOCOMPRESS COMPRESS BASIC; Table altered. SQL> analyze table TEST_NOCOMPRESS compute statistics; Table analyzed. SQL> select table_name, blocks, pct_free, compression, compress_for from user_tables where table_name = 'TEST_NOCOMPRESS'; TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR ------------------------------ ---------- ---------- -------- ------------------------------ TEST_NOCOMPRESS 173 10 ENABLED BASIC -- NOTE THAT: The table is compressed but its size is not reduced until the next sentence. SQL> alter table TEST_NOCOMPRESS MOVE; Table altered. SQL> analyze table TEST_NOCOMPRESS compute statistics; Table analyzed. SQL> select table_name, blocks, pct_free, compression, compress_for from user_tables where table_name = 'TEST_NOCOMPRESS'; TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR ------------------------------ ---------- ---------- -------- ------------------------------ TEST_NOCOMPRESS 39 0 ENABLED BASIC SQL>
To wrap up.
It’s important to note that while compression can provide substantial benefits, it’s not always the best solution for every scenario. The choice to use compression should be based on various factors such as the nature of the data, the storage capacity, and the specific requirements of the database operations.
Stay tuned! This is a recurring topic in the Oracle Database Administration Professional certification exams (1Z0-082 and 1Z0-083), so we will continue to delve deeper into the world of data compression in Oracle databases, exploring its workings, benefits, and best practices. Embark on this journey to make the most out of our data storage.
Resources:
- Gregg Christman: When to Use the Various Types of Oracle Data Compression.
- Oracle VLDB and Partitioning Guide: Partitioning and Table Compression.
Was this post helpful? Consider subscribing or leaving a comment.
Oracle Essentials: Oracle Database 12c
$49.99 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
Upgrading Oracle Databases: Oracle Database New Features
$19.99 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)
In the Company of Giants: Candid Conversations With the Visionaries of the Digital World
$26.97 (as of December 20, 2024 21:38 GMT +00:00 - More infoProduct prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on [relevant Amazon Site(s), as applicable] at the time of purchase will apply to the purchase of this product.)