Home > EXADATA, HCC (Hybrid Columnar Compression) > Testing and Comparing HCC (Hybrid Columnar Compression) Compression Methods on Exadata X3 Quarter

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

Talip Hakan Öztürk

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: