In an earlier article I covered Hybrid Columnar Compression, an option specific to Exadata and systems using Exadata storage. Those aren’t the only compression options; two others, BASIC and OLTP, are available outside of the Advanced Compression licensing and can be used on non-Exadata storage. Let’s go over the HCC options again and compare those to BASIC and OLTP compression. We’ll start with BASIC and OLTP compression and move into a review of the HCC types.
Oracle 11.2.0.x provides, out of the box, two types of table compression, BASIC and OLTP. BASIC is exactly what its name says, compressed with a basic compression algorithm. Let’s see what space savings BASIC can provide:
SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 713031680 SALGRADE 65536 SQL> alter table emp move compress; Table altered. SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 184549376 SALGRADE 65536 SQL>
So far, so good. Let’s now update some data in the EMP table and see what happens to the consumed space:
SQL> update emp set sal=5001 where sal=5000; 1048576 rows updated. SQL> commit; Commit complete. SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 234881024 SALGRADE 65536 SQL>
The update caused Oracle to uncompress the rows being updated and, thus increase the space consumed by the table. You can re-compress the table after updates but that could get to be a daunting task, requiring a stored procedure for inserts, updates and deletes to the affected table or tables. A second option is a scheduled job run late at night to re-compress the compressed tables.
OLTP compression works a bit differently, as it compresses the data but will, when affected data blocks are marked as full, re-compress the block automatically. Let’s look at the same table using OLTP compression. First let’s uncompress the table:
SQL> alter table emp move nocompress; Table altered. SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 645922816 SALGRADE 65536 SQL>
It’s interesting to note that the uncompressed table size has decreased slightly from its original value, possibly because unnecessary NULL bytes have been removed (this is a guess). Compressing for OLTP and updating the same rows produces these results:
SQL> alter table emp move compress for oltp; Table altered. SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 184549376 SALGRADE 65536 SQL> update emp set sal=5000 where sal=5001; 1048576 rows updated. SQL> commit; Commit complete. SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 201326592 SALGRADE 65536 SQL>
Notice that the compressed size of the OLTP table is initially the same as it was for BASIC compression; the difference is in the compressed size after the update. It’s the OLTP compression mechanism that does this, as some of the updated blocks are now full and triggered the automatic re-compression of the data.
HCC works differently than Basic or OLTP compression as it re-organizes data into Compression Units (CU). Remember that there are two types of HCC compression, QUERY and ARCHIVE, with two levels of LOW and HIGH for each. Using the same examples from a previous article let’s look at these compression types in action. First in line is QUERY:
SQL> SQL> -- SQL> -- Current storage for the EMP table SQL> -- (this is simply a test table for this example) SQL> -- SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 713031680 EMP_IDX 478150656 SALGRADE 65536 Elapsed: 00:00:00.82 SQL> SQL> -- SQL> -- Compress the table for query high (use HCC) SQL> -- SQL> -- Note elapsed time to compress SQL> -- SQL> alter table emp move compress for query high; Table altered. Elapsed: 00:00:35.65 SQL> SQL> -- SQL> -- Index is now invalid SQL> -- SQL> -- Must rebuild to make it usable SQL> -- SQL> -- Note elapsed time SQL> -- SQL> alter index emp_idx rebuild; Index altered. Elapsed: 00:01:13.70 SQL> SQL> -- SQL> -- Current compression type, storage for table/index SQL> -- initially after compression is enabled SQL> -- SQL> select table_name, compression, compress_for 2 from user_tables; TABLE_NAME COMPRESS COMPRESS_FOR -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DEPT DISABLED BONUS DISABLED SALGRADE DISABLED DUMMY DISABLED EMP ENABLED QUERY HIGH Elapsed: 00:00:00.20 SQL> SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 8388608 EMP_IDX 260046848 SALGRADE 65536 Elapsed: 00:00:00.03 SQL>
The resulting size is much smaller than either of the Basic or OLTP compression options. Even at the Query Low compression rate the size is still less than OLTP compression can provide:
SQL> SQL> -- SQL> -- Initial storage SQL> -- SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 713031680 EMP_IDX 478150656 SALGRADE 65536 Elapsed: 00:00:00.25 SQL> SQL> -- SQL> -- Compress for QUERY LOW SQL> -- SQL> -- Note elapsed time SQL> -- SQL> alter table emp move compress for query low; Table altered. Elapsed: 00:00:16.16 SQL> SQL> alter index emp_idx rebuild; Index altered. Elapsed: 00:00:43.08 SQL> SQL> -- SQL> -- These figures are the same as those generated SQL> -- AFTER the HCC compressed data was updated the first time SQL> -- SQL> select table_name, compression, compress_for 2 from user_tables; TABLE_NAME COMPRESS COMPRESS_FOR -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DEPT DISABLED BONUS DISABLED SALGRADE DISABLED DUMMY DISABLED EMP ENABLED QUERY LOW Elapsed: 00:00:00.02 SQL> SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 14680064 EMP_IDX 260046848 SALGRADE 65536 Elapsed: 00:00:00.02 SQL>
The QUERY compression type is definitely a space saver, but note that after updates to a table compressed with either QUERY LOW or QUERY HIGH the compression behavior reverts to the OLTP re-compression mechanism.
The HCC compression type ARCHIVE is definitely the most aggressive in terms of space savings, but it’s also intended for data that is designated read-only and is or will be archived for occasional use:
SQL> -- SQL> -- Current storage for the EMP table SQL> -- (this is simply a test table for this example) SQL> -- SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 713031680 EMP_IDX 478150656 SALGRADE 65536 Elapsed: 00:00:00.02 SQL> SQL> -- SQL> -- Compress the table for archive high SQL> -- SQL> -- Note elapsed time to compress SQL> -- SQL> alter table emp move compress for archive high; Table altered. Elapsed: 00:00:38.55 SQL> SQL> -- SQL> -- Index is now invalid SQL> -- SQL> -- Must rebuild to make it usable SQL> -- SQL> -- Note elapsed time SQL> -- SQL> alter index emp_idx rebuild; Index altered. Elapsed: 00:00:39.45 SQL> SQL> -- SQL> -- Current compression type, storage for table/index SQL> -- initially after compression is enabled SQL> -- SQL> select table_name, compression, compress_for 2 from user_tables; TABLE_NAME COMPRESS COMPRESS_FOR -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DEPT DISABLED BONUS DISABLED SALGRADE DISABLED DUMMY DISABLED EMP ENABLED ARCHIVE HIGH Elapsed: 00:00:00.02 SQL> SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 4194304 EMP_IDX 260046848 SALGRADE 65536 Elapsed: 00:00:00.01 SQL>
The space savings are substantial, taking the table from its original size of 680 megabytes down to 4 megabytes, a savings of 99.41 percent. Using ARCHIVE LOW instead of ARCHIVE HIGH still produces impressive results:
SQL> -- SQL> -- Initial storage SQL> -- SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 713031680 EMP_IDX 478150656 SALGRADE 65536 Elapsed: 00:00:01.31 SQL> SQL> -- SQL> -- Compress for ARCHIVE LOW SQL> -- SQL> -- Note elapsed time SQL> -- SQL> alter table emp move compress for archive low; Table altered. Elapsed: 00:00:34.16 SQL> SQL> alter index emp_idx rebuild; Index altered. Elapsed: 00:00:48.44 SQL> SQL> -- SQL> -- These figures are the same as those generated SQL> -- AFTER the HCC compressed data was updated the first time SQL> -- SQL> select table_name, compression, compress_for 2 from user_tables; TABLE_NAME COMPRESS COMPRESS_FOR -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- DEPT DISABLED BONUS DISABLED SALGRADE DISABLED DUMMY DISABLED EMP ENABLED ARCHIVE LOW Elapsed: 00:00:00.03 SQL> SQL> select segment_name, sum(bytes) total_space 2 from user_segments 3 group by segment_name; SEGMENT_NAME TOTAL_SPACE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - -- -- -- -- -- - DEPT 65536 DUMMY 65536 EMP 8388608 EMP_IDX 260046848 SALGRADE 65536 Elapsed: 00:00:00.02 SQL>
Using ARCHIVE LOW the table ends up twice as large as it did using ARCHIVE HIGH but the table is 98.82 percent smaller than it was before compression. This is the same level of compression afforded by QUERY HIGH. The same caveat for QUERY level compression also applies to ARCHIVE; updates to a table compressed either ARCHIVE LOW or ARCHIVE HIGH cause Oracle to revert to the OLTP re-compression mechanism. Also note that the reported compression type in the data dictionary does NOT change when this occurs.
Compression can be an excellent tool to save database storage, but you need to be aware that the compression levels can change for updated tables when running Exadata and using any of the HCC compression types. Also it’s good to be aware that BASIC compression won’t automatically re-compress updated data; maintaining a reasonable compression ratio will require some sort of manual or scheduled intervention, so using OLTP compression on active tables is the best choice if you are planning on using table compression.