Compression Methods on Exadata: Create compression table using CREATE TABLE – Part 1 of 6

dataCompression

I have done compression works in the past on Exadata, but never done a benchmark to compare different types of compression methods on Exadata. Of course, I don’t need a tank shown above to do the compression. But you can see the tank does compression very hard.

Let’s start from the basic ones. The default compression is BASIC method, which was introduced before 11g. In 11g, OLTP compression is used. On Exadata, Oracle uses Hybrid Columnar Compression (HCC). It includes compression methods like Query Low, Query High, Archive Low, and Archive High. This is a six part blog series on compression methods on Exadata.

The tests were run on Exadata X3 Eighth Rack. First, let create a test table with 10 million rows. The create table script is based on Tom Kyte’s test case with slight modifications. (http://asktom.oracle.com)

create table testcp
as
select rownum id, a.*
  from all_objects a
 where 1=0;

declare
    l_cnt  number;
    l_rows number := 10000000;
begin
    -- Copy ALL_OBJECTS
    insert /*+ append */
    into testcp
    select rownum, a.*
      from all_objects a;
    l_cnt := sql%rowcount;
    commit;

    -- Generate Rows
    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into testcp
        select rownum+l_cnt,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
          from testcp
         where rownum <= l_rows-l_cnt;         l_cnt := l_cnt + sql%rowcount;         commit;     end loop; end; / 

Let me verify the row count.

 WZHOU@dbm1> select count(*) from testcp;

COUNT(*)
----------
 10000000

My Enkitec colleagues have many nice scripts and I am going to use a few of them in my tests. The complete scripts used here are listed at Script page.

Ok, Let’s look at the size of the testcp table.

WZHOU@dbm1>  @table_size
Enter value for owner: WZHOU
Enter value for table_name: TESTCP
Enter value for type:

OWNER	SEGMENT_NAME	TYPE	     TOTALSIZE_MEGS TABLESPACE_NAME
------- --------------- ------------ -------------- -------------------------
WZHOU	TESTCP          TABLE              1,152.0  USERS
                                         ----------
sum                                        1,152.0

BASIC Compression:

WZHOU@dbm1> create table testcp_basic compress as select * from testcp where 1=2;
Table created.
Elapsed: 00:00:00.36

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='TESTCP_BASIC';

TABLE_NAME		       LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TESTCP_BASIC                   YES ENABLED  BASIC
Elapsed: 00:00:00.02

WZHOU@dbm1> insert /*+ append */ into testcp_basic select * from testcp;
10000000 rows created.
Elapsed: 00:00:28.18

WZHOU@dbm1> commit;
Commit complete.
Elapsed: 00:00:00.00

OLTP Compression:

WZHOU@dbm1> create table testcp_oltp compress for oltp as select * from testcp where 1=2;
Table created.
Elapsed: 00:00:00.33

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
where table_name='TESTCP_OLTP';
TABLE_NAME		       LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TESTCP_OLTP            YES ENABLED  OLTP
Elapsed: 00:00:00.02

WZHOU@dbm1> insert /*+ append */ into testcp_oltp select * from testcp;
10000000 rows created.
Elapsed: 00:00:28.87

WZHOU@dbm1> commit;
Commit complete.
Elapsed: 00:00:00.01

Exadata Query Low Compression

WZHOU@dbm1> create table testcp_query_low compress for query low as select * from testcp where 1=2;
Table created.
Elapsed: 00:00:00.34

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
where table_name='TESTCP_QUERY_LOW';
TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TESTCP_QUERY_LOW       YES ENABLED  QUERY LOW
Elapsed: 00:00:00.02

WZHOU@dbm1> insert /*+ append */ into testcp_query_low select * from testcp;
10000000 rows created.
Elapsed: 00:00:15.41

WZHOU@dbm1> commit;
Commit complete.
Elapsed: 00:00:00.01

Exadata Query High Compression

WZHOU@dbm1> create table testcp_query_high compress for query high as select * from testcp where 1=2;
Table created.
Elapsed: 00:00:00.34

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='TESTCP_QUERY_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TESTCP_QUERY_HIGH      YES ENABLED  QUERY HIGH
Elapsed: 00:00:00.02

WZHOU@dbm1> insert /*+ append */ into testcp_query_high select * from testcp;
10000000 rows created.
Elapsed: 00:00:27.59

WZHOU@dbm1> commit;
Commit complete.
Elapsed: 00:00:00.00

Exadata Archive Low Compression

WZHOU@dbm1> create table testcp_archive_low compress for archive low as select * from testcp where 1=2;
Table created.
Elapsed: 00:00:00.34

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='TESTCP_ARCHIVE_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TESTCP_ARCHIVE_LOW     YES ENABLED  ARCHIVE LOW
Elapsed: 00:00:00.02

WZHOU@dbm1> insert /*+ append */ into testcp_archive_low select * from testcp;
10000000 rows created.
Elapsed: 00:00:28.97

WZHOU@dbm1> commit;
Commit complete.
Elapsed: 00:00:00.01

Exadata Archive High Compression

WZHOU@dbm1> create table testcp_archive_high compress for archive high as select * from testcp where 1=2;
Table created.
Elapsed: 00:00:00.34

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='TESTCP_ARCHIVE_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
TESTCP_ARCHIVE_HIGH        YES ENABLED  ARCHIVE HIGH
Elapsed: 00:00:00.02

WZHOU@dbm1> insert /*+ append */ into testcp_archive_high select * from testcp;
10000000 rows created.
Elapsed: 00:02:04.72

WZHOU@dbm1> commit;
Commit complete.
Elapsed: 00:00:00.01

So far, we have created six different compression tables. Let’s look at the table size for each of them.

WZHOU@dbm1> @table_size
Enter value for owner: WZHOU
Enter value for table_name: TESTCP%
Enter value for type:

OWNER  SEGMENT_NAME         TYPE    TOTALSIZE_MEGS TABLESPACE_NAME
------ -------------------- ------- -------------- ---------------
WZHOU  TESTCP_QUERY_HIGH    TABLE            59.0 USERS
WZHOU  TESTCP_ARCHIVE_HIGH  TABLE            59.0 USERS
WZHOU  TESTCP_ARCHIVE_LOW   TABLE            59.0 USERS
WZHOU  TESTCP_QUERY_LOW     TABLE           161.0 USERS
WZHOU  TESTCP_BASIC         TABLE           384.0 USERS
WZHOU  TESTCP_OLTP          TABLE           424.0 USERS
WZHOU  TESTCP               TABLE         1,152.0 USERS
                                       --------------
sum                                       2,298.0

7 rows selected.

It’s pretty amazing to see Exadata HCC compressions can get 10~20 compression ratio. From the timing perspective, only Archive High takes three times more than other compression methods.

Advertisements

One thought on “Compression Methods on Exadata: Create compression table using CREATE TABLE – Part 1 of 6

  1. Pingback: Compression Methods on Exadata: Compression on partition tables – Part 4 of 6 | My Big Data World

Leave a Reply

Please log in using one of these methods to post your comment:

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