Apr 12, 2017 3:30:53 PM by Dan Hotka
Did you know that Oracle supports compression in various forms in the database? You can compress an entire tablespace (all of its contents), a table, or part of a table (at the partition level). Using sub-partitioning, the sub-partitions can be either compressed or not compressed .
This quick blog hopes to introduce you to Table Compression, why and when you would use it, along with some useful syntax.
Compression is all about reducing the storage requirements for the data within the table. The higher the compression, the greater the storage savings, and the longer it will take to uncompress (perhaps).
I’m a huge fan of compression. Any computer’s longest task is physical I/O operations, so anything you can do to reduce physical I/O’s will more than likely increase query performance.
Oracle’s compression is designed so that you can leave frequently used data in an uncompressed format, somewhat older data in a compressed format, and infrequently accessed data in a tighter compression for additional savings in disk storage. Why? Maybe your shop has requirements to have two years of customer information available but most people are looking at the data on a quarter-by-quarter basis and seldom look in the prior year. You can set up a compression scheme that allows for the data to be located on a cheaper/slower device in a highly compressed form. The same SQL is used to access the data but it just might take a bit longer. This is okay…you have met the requirements and saved space on your expensive RAID device.
There are two basic types of table compression: Basic Table Compression and Advanced Row Compression. There a third type of compression useful for data warehouse applications: Hybrid Columnar Compression.
Basic Table Compression is designed for bulk load operations. Oracle does not compress any data on the usual DML type operations (insert/update/delete). It does compress data using the DIRECT PATH INSERT syntax/technology. This includes direct path loading, create table as select statement, APPEND hint/syntax, and parallel inserts.
Advanced Row Compression DOES compress data using conventional SQL (DML) operations. These include INSERT /UPDATE/DELETE operations as well as array inserts.
Both the basic and the advanced row compression compress the data with minimal CPU utilization and tend to reduce physical I/O operations.
Alter table emp ROW STORE COMPRESS BASIC;
Alter table emp ROW STORE COMPRESS ADVANCED;
Create table emp (columns and such)
Partition by range (hireyear)
(partition Year2015 values less than (2016) ROW STORE COMPRESS ADVANCED );
(partition Year2016 values less than (2017) ROW STORE COMPRESS BASIC ),
(partition Year2017 values less than (MAXVALUE) );
In the above example, if compression is not specified in the partitions, the rows will not be compressed.
There are a couple more useful table compression implementations for Oracle and these are designed for data warehouse.
Hybrid Columnar Compression comes in two flavors: compress for archive and compress for query. Each has two options: low and high. Low is useful where there might be some DML operations such as additional data loads. High is useful when no DML is expected at all.
FOR ARCHIVE should be used when the data isn’t going to be accessed. The HIGH feature will compress the data at the highest level and should be used on data that will be rarely accessed, if accessed at all.
FOR QUERY is used for most data warehouse operations. This allows for good query performance. The LOW option might help with better load times. The HIGH option might be useful if the data isn’t going to change much.
Alter table emp COLUMN STORE COMPRESS FOR ARCHIVE HIGH;
Alter table emp COLUMN STORE COMPRESS FOR QUERY LOW;
Just like the table compression, these compression methods can be mixed for best results at the partition and subpartition levels.
Use this SQL to see what, if any, compression levels are in use on your objects.
Select TABLE_NAME, COMPRESSION, COMPRESS_FOR from USER_TABLES;
For partitioned objects, use this syntax:
Select TABLE_NAME, PARTITION_NAME, COMPRESSION, COMPRESS_FOR from USER_TAB_PARTITIONS;
Want to change the compression scheme? Use the ALTER TABLE …MOVE ONLINE or MOVE PARTITION ONLINE…with different compression syntax.
You can also see the level of compression at the row level using the function DBMS_COMPRESSION.GET_COMPRESSION_TYPE. Use Toad and do a describe on this function.
A value of:
= compress advanced
= for query high
= for query low
= for archive high
= for archive low
= compress basic
I hope you find this information useful in your day-to-day use of the Oracle RDBMS.
Written by Dan Hotka
Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.
Dan's most recent book is Toad for Oracle Unleashed