Testing and Comparing HCC (Hybrid Columnar Compression) Compression Methods on Exadata X3 Quarter

BASIC compression method is suggested in data warehouse environments prior to Oracle 11g. With Oracle 11g, OLTP compression method is recommended for OLTP databases. Now, with Oracle Exadata we can use QUERY LOW, QUERY HIGH, ARCHIVE LOW, and ARCHIVE HIGH compression methods called HCC (Hybrid Columnar Compression). I tested and compared compression methods on Exadata X3 Quarter Rack.

Now let’s move in our tests.

I created a table named TALIP_TEST in 3.8 GB size.

SQL> select segment_name, bytes/1024/1024 as size_MB from dba_segments
2 where segment_name=’TALIP_TEST’;

SEGMENT_NAME SIZE_MB
———— ———-
TALIP_TEST 3809

SQL> select count(*) from talip_test;

COUNT(*)
———-
22919193

First, let’s try the BASIC compression method. To do this, use the COMPRESS keyword.

SQL> create table talip_test_basic nologging compress as select * from talip_test where 1=2;

Table created.

SQL> select table_name,logging,compression,compress_for from dba_tables
2 where table_name=’TALIP_TEST_BASIC’;

TABLE_NAME LOGGING COMPRESS COMPRESS_FOR
—————————— ———— ————– ————
TALIP_TEST_BASIC NO ENABLED BASIC

SQL> set timing on
SQL> insert /*+ append */ into talip_test_basic select * from talip_test;

22919193 rows created.

Elapsed: 00:01:32.28
SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

let’s use COMPRESS FOR OLTP keyword to use for OLTP compression.

SQL> create table talip_test_oltp compress for oltp as select * from talip_test where 1=2;

Table created.

SQL> select table_name,logging,compression,compress_for from dba_tables
2 where table_name=’TALIP_TEST_OLTP’;

TABLE_NAME LOGGING COMPRESS COMPRESS_FOR
—————————— ———— ————- ————
TALIP_TEST_OLTP NO ENABLED OLTP

SQL> insert into talip_test_oltp select * from talip_test;

22919193 rows created.

Elapsed: 00:04:27.09
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

Now let’s move on Exadata compression methods. Let’s try COMPRESS FOR QUERY LOW keyword.

SQL> create table talip_test_query_low nologging compress for query low as select * from talip_test where 1=2;
Table created.

SQL> select table_name,logging,compression,compress_for from dba_tables
2 where table_name=’TALIP_TEST_QUERY_LOW’;

TABLE_NAME LOGGING COMPRESS COMPRESS_FOR
—————————— ——- ——– ————
TALIP_TEST_QUERY_LOW NO ENABLED QUERY LOW

SQL> insert /*+ append */ into talip_test_query_low select * from talip_test;

22919193 rows created.

Elapsed: 00:01:04.92
SQL> commit;

Commit complete.
Elapsed: 00:00:00.01

If we want to save more space, we need to use COMPRESS FOR QUERY HIGH method. But here’s the insert time will be longer.

SQL> create table talip_test_query_high nologging compress for query high as select * from talip_test where 1=2;

Table created.

SQL> select table_name,logging,compression,compress_for from dba_tables
2 where table_name=’TALIP_TEST_QUERY_HIGH’;

TABLE_NAME LOGGING COMPRESS COMPRESS_FOR
—————————— ———– ————— ————
TALIP_TEST_QUERY_HIGH NO ENABLED QUERY HIGH

SQL> insert /*+ append */ into talip_test_query_high select * from talip_test;

22919193 rows created.

Elapsed: 00:02:16.49
SQL> commit;

Commit complete.
Elapsed: 00:00:00.01

If we have archive data then we can use compress for archive method to save more space and less insert time.

SQL> create table talip_test_archive_low nologging compress for archive low as select * from talip_test where 1=2;

Table created.

SQL> select table_name,logging,compression,compress_for from dba_tables
2 where table_name=’TALIP_TEST_ARCHIVE_LOW’;

TABLE_NAME LOGGING COMPRESS COMPRESS_FOR
———————————— ———– ————– ————
TALIP_TEST_ARCHIVE_LOW NO ENABLED ARCHIVE LOW

SQL> insert /*+ append */ into talip_test_archive_low select * from talip_test;

22919193 rows created.

Elapsed: 00:03:05.70
SQL> commit;

Commit complete.
Elapsed: 00:00:00.01

If the time is not so important and we want to save more space then we use the keyword HIGH COMPRESS FOR ARCHIVE.

SQL> create table talip_test_archive_high nologging compress for archive high as select * from talip_test where 1=2;
Table created.

SQL> select table_name,logging,compression,compress_for from dba_tables
2 where table_name=’TALIP_TEST_ARCHIVE_HIGH’;

TABLE_NAME LOGGING COMPRESS COMPRESS_FOR
————————————— ———– ————— ————
TALIP_TEST_ARCHIVE_HIGH NO ENABLED ARCHIVE HIGH

SQL> insert /*+ append */ into talip_test_archive_high select * from talip_test;

22919193 rows created.

Elapsed: 00:12:02.97
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

Finally, let’s see table sizes.

SQL> set line 1000
SQL> select segment_name,bytes/1024/1024 as size_MB from dba_segments
2 where segment_name like ‘TALIP_TEST%’ order by 1;

SEGMENT_NAME SIZE_MB

TALIP_TEST 3809

TALIP_TEST_ARCHIVE_HIGH 424

TALIP_TEST_ARCHIVE_LOW 488

TALIP_TEST_BASIC 2500

TALIP_TEST_OLTP 2997

TALIP_TEST_QUERY_HIGH 512

TALIP_TEST_QUERY_LOW 856

7 rows selected.

Let me add the compression times and our table looks as follows.

SEGMENT_NAME	SIZE_MB	TIME
TALIP_TEST	3809
TALIP_TEST_ARCHIVE_HIGH	424	00:12:02.97
TALIP_TEST_ARCHIVE_LOW	488	00:03:05.70
TALIP_TEST_BASIC	2500	00:01:32.28
TALIP_TEST_OLTP	2997	00:04:27.09
TALIP_TEST_QUERY_HIGH	512	00:02:16.49
TALIP_TEST_QUERY_LOW	856	00:01:04.92