Compression and Advanced Compression in Oracle 11.2

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.

See all articles by David Fitzjarrell

Compression and Advanced Compression in Oracle 11.2

David Fitzjarrell

Recommended for you...

Company

Categories