Compression Methods on Exadata: Compression on partition tables – Part 4 of 6

partitiondataIn the part 1, part 2, part 3 of the blog series, I show the examples to create compress tables using CREATE TABLE and ALTER TABLE MOVE and how to improve the compression performance using parallelism. For big database, partition table is quite common. This post shows how partition table behaves for different compression methods. There is a nice thing about partition table is that you can compress certain partitions while leave other partition uncompressed. This is very useful feature as you might need to compress historical data and only keep the current partition uncompressed. Printer manufacturer Brother has an interesting newspaper ads for its USP product shown above. Obviously this guy needs a better partition strategy as well as compression approaches to save his files digitally.

First let’s create a partition table. I made a slight change in the create table script from part 1.

CREATE TABLE PARTAB
(
    ID NUMBER,
    OWNER VARCHAR2(30) NOT NULL ENABLE,
    OBJECT_NAME VARCHAR2(30) NOT NULL ENABLE,
    SUBOBJECT_NAME VARCHAR2(30),
    OBJECT_ID NUMBER NOT NULL ENABLE,
    DATA_OBJECT_ID NUMBER,
    OBJECT_TYPE VARCHAR2(19),
    CREATED DATE NOT NULL ENABLE,
    LAST_DDL_TIME DATE NOT NULL ENABLE,
    TIMESTAMP VARCHAR2(19),
    STATUS VARCHAR2(7),
    TEMPORARY VARCHAR2(1),
    GENERATED VARCHAR2(1),
    SECONDARY VARCHAR2(1),
    NAMESPACE NUMBER NOT NULL ENABLE,
    EDITION_NAME VARCHAR2(30)
)
PARTITION BY RANGE (OBJECT_ID)
    (PARTITION P1 values less than (1500),
    PARTITION P2 values less than (4000),
    PARTITION P3 values less than (6000),
    PARTITION P4 values less than (maxvalue)
    );

Then, populate some data with 10 million rows in total.

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

    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into partab
        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 partab
         where rownum <= l_rows-l_cnt;         l_cnt := l_cnt + sql%rowcount;         commit;     end loop; end; / 

Check out the row count and table size.

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

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

Elapsed: 00:00:07.43

WZHOU@dbm1> select count(*) from partab partition(p1);

  COUNT(*)
----------
   1364992

Elapsed: 00:00:00.08

WZHOU@dbm1> select count(*) from partab partition(p2);

  COUNT(*)
----------
   2560000

Elapsed: 00:00:00.15

WZHOU@dbm1> select count(*) from partab partition(p3);

  COUNT(*)
----------
   1445504

Elapsed: 00:00:00.08

WZHOU@dbm1> select count(*) from partab partition(p4);

  COUNT(*)
----------
   4629504

Elapsed: 00:00:00.27

Before doing the compression work, let me check out the table size and partition size. You can find part_size.sql from the Script page. This script is also one of the Enkitec scripts I use frequently.

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

OWNER  SEGMENT_NAME TYPE             TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ ---------------- -------------- ------------------------------
WZHOU        PARTAB TABLE PARTITION       1,112.0   USERS
                                     --------------
sum                                       1,112.0

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name:
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P1       TABLE PARTITION       152.0    USERS
WZHOU  PARTAB             P2       TABLE PARTITION       280.0    USERS
WZHOU  PARTAB             P3       TABLE PARTITION       152.0    USERS
WZHOU  PARTAB             P4       TABLE PARTITION       528.0    USERS
                                                   --------------
sum                                                    1,112.0

Right now, let me start with the Basic compression.

Basic Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 COMPRESS;

Table altered.

Elapsed: 00:00:14.00

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
    where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             BASIC

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION       192.0    USERS
                                                   --------------
sum                                                      192.0

There is little change we need to make before we proceed to the next test. We need to restore this partition to the non-compression stage by using nocompress keyword.

OLTP Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:05.99

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for oltp;

Table altered.

Elapsed: 00:00:14.22
WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS     where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             OLTP

Elapsed: 00:00:00.01

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION       192.0    USERS
                                                   --------------
sum                                                      192.0

Query Low Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:06.01
WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for query low;

Table altered.

Elapsed: 00:00:06.83
WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
    where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             QUERY LOW

Elapsed: 00:00:00.01

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION        80.0    USERS
                                                   --------------
sum                                                       80.0

Query High Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:05.26

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for query high;

Table altered.

Elapsed: 00:00:13.08

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
    where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             QUERY HIGH

Elapsed: 00:00:00.01

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION        32.0    USERS
                                                   --------------
sum                                                       32.0

Archive Low Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:05.09

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for archive low;

Table altered.

Elapsed: 00:00:13.60

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
  where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             ARCHIVE LOW

Elapsed: 00:00:00.00

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION         32.0   USERS
                                                   --------------
sum                                                        32.0

Archive High Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:04.93

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for archive high;

Table altered.

Elapsed: 00:01:13.33

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
  where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             ARCHIVE HIGH

Elapsed: 00:00:00.01

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION        24.0    USERS
                                                   --------------
sum                                                       24.0

The timing is quite different from 13 seconds on majority of compression methods to about 73 seconds on Archive High method, about 500% time increase. The compression ratio is similar to what we see in the non-partition table. The partition size drops from 528 MB to about 25~40 MB for most of compression methods.

There is another interest aspect in partition tables is that you can use different compression methods for different partitions. This is useful that allows to compress the recent data in faster methods while very old historical data can use a method with longer processing time, but higher compression ratio. Here is the test to show different compression methods for different partitions.

WZHOU@dbm1> ALTER TABLE partab MOVE partition p1 compress for query low;

Table altered.

Elapsed: 00:00:01.88

WZHOU@dbm1> ALTER TABLE partab MOVE partition p2 compress for query high;

Table altered.

Elapsed: 00:00:05.75

WZHOU@dbm1> ALTER TABLE partab MOVE partition p3 compress for archive low;

Table altered.

Elapsed: 00:00:02.34

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
     where table_name = 'PARTAB';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P1             QUERY LOW
WZHOU       PARTAB     P2             QUERY HIGH
WZHOU       PARTAB     P3             ARCHIVE LOW
WZHOU       PARTAB     P4             ARCHIVE HIGH

Elapsed: 00:00:00.04

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name:
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P1       TABLE PARTITION        16.0    USERS
WZHOU  PARTAB             P2       TABLE PARTITION        16.0    USERS
WZHOU  PARTAB             P3       TABLE PARTITION         8.0    USERS
WZHOU  PARTAB             P4       TABLE PARTITION        24.0    USERS
                                                   --------------
sum                                                       64.0

See enough code? In the next part, I am going to go back the basic stuff and discuss the layout of HCC as well as some recommendation when to use and when not to use HCC.

Advertisements

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