Oracle Subpartition Compression


Oracle started doing data compression in Oracle 9, at first it was a bit buggy and limited, but it really improved over time. There are a lot of great articles how to compress tables/partitions or tablespaces. I was a bit surprised that I was not able to find almost any information on subpartition compression. Here are few interesting tips on subpartition compression in Oracle 10.2.0.4

I have been using compression heavily on partitioned and subpartitioned tables. The largest subpartitioned compressed table I created (DDL as well as ETL) is +/- 2TB in size (customer level P&L). It works like a charm and I never really had any issues (it worked on 9.2, it works even better on 10.2). I’ve seen however quite a lot of problems in 9.2 in general (read a lot of documentation before you implement it – it can save  a lot of space but can flash back pretty hard).

easy way

of course the easiest way is to create a table with compressed partitions, there is really nothing to talk about here …

CREATE TABLE JIRI.DAILY_BALANCE_ROLL_FORWARD
(
  YEAR_MONTH_NBR                NUMBER(6)         NOT NULL,
  BALANCE_ROLLFORWARD_SECTION   VARCHAR2(30 BYTE) NOT NULL,
  TOTAL_BALANCE                 NUMBER(5,2)       NOT NULL
)
PARTITION BY RANGE (YEAR_MONTH_NBR)
SUBPARTITION BY LIST (BALANCE_ROLLFORWARD_SECTION)
( 
  PARTITION JAN2008 VALUES LESS THAN (200802)
    COMPRESS
  ( SUBPARTITION JAN2008_PART1 VALUES ('ENDING OUTSTANDING BALANCE') ,
    SUBPARTITION JAN2008_PART2 VALUES ('TRANSACTION ACTIVITY') 
  ), 
  PARTITION FEB2008 VALUES LESS THAN (200803)
    COMPRESS
  ( SUBPARTITION FEB2008_PART1 VALUES ('ENDING OUTSTANDING BALANCE') ,
    SUBPARTITION FEB2008_PART2 VALUES ('TRANSACTION ACTIVITY')      
  )
 
) ;

and now the trick

the fun starts in the real world… of course you will never even in million years create compressed table, you usually create uncompressed table (DML is way faster in uncompressed tables) and few months later start running out of space. Let’s create the table one more time (this time uncompressed) and then try to compress JAN2008 using ALTER command (well you need actually 2 commands).

CREATE TABLE JIRI.DAILY_BALANCE_ROLL_FORWARD
(
  YEAR_MONTH_NBR                NUMBER(6)         NOT NULL,
  BALANCE_ROLLFORWARD_SECTION   VARCHAR2(30 BYTE) NOT NULL,
  TOTAL_BALANCE                 NUMBER(5,2)       NOT NULL
)
PARTITION BY RANGE (YEAR_MONTH_NBR)
SUBPARTITION BY LIST (BALANCE_ROLLFORWARD_SECTION)
( 
  PARTITION JAN2008 VALUES LESS THAN (200802)
    NOCOMPRESS
  ( SUBPARTITION JAN2008_PART1 VALUES ('ENDING OUTSTANDING BALANCE') ,
    SUBPARTITION JAN2008_PART2 VALUES ('TRANSACTION ACTIVITY') 
  ), 
  PARTITION FEB2008 VALUES LESS THAN (200803)
    NOCOMPRESS
  ( SUBPARTITION FEB2008_PART1 VALUES ('ENDING OUTSTANDING BALANCE') ,
    SUBPARTITION FEB2008_PART2 VALUES ('TRANSACTION ACTIVITY')      
  )
 
) ;

you first compression bet is probably something like this

ALTER TABLE JIRI.DAILY_BALANCE_ROLL_FORWARD MOVE PARTITION JAN2008 COMPRESS; 
ORA-14257: cannot move partition other than a Range or Hash partition

ok, here is the second try

ALTER TABLE JIRI.DAILY_BALANCE_ROLL_FORWARD MOVE SUBPARTITION JAN2008_PART1 COMPRESS;
ORA-14160: this physical attribute may not be specified for a table subpartition

ok, there must be a way, right? of course there is, but it is two step process

ALTER TABLE JIRI.DAILY_BALANCE_ROLL_FORWARD MODIFY PARTITION    JAN2008 COMPRESS;
ALTER TABLE JIRI.DAILY_BALANCE_ROLL_FORWARD MOVE   SUBPARTITION JAN2008_PART1;

BINGO !! It’s that easy. The second ALTER can be done in parallel if you wish

ALTER TABLE JIRI.DAILY_BALANCE_ROLL_FORWARD MOVE   SUBPARTITION JAN2008_PART1 PARALLEL 4;

you can run the following query to check the space change (use DBA_SEGMENTS or USER_SEGMENTS)

SELECT A.*
  FROM DBA_SEGMENTS A
 WHERE SEGMENT_NAME = 'DAILY_BALANCE_ROLL_FORWARD'
   AND OWNER        = 'JIRI'
Advertisements

6 thoughts on “Oracle Subpartition Compression

  1. HMark says:

    Jiri,
    Nice post, I’ve a question…
    Why do we need to compress first ?
    I think “move subpartition”
    ALTER TABLE JIRI.DAILY_BALANCE_ROLL_FORWARD MOVE SUBPARTITION JAN2008_PART1;
    also does the same thing ie compress the data.

  2. jiri says:

    yes MOVE does the actual compression, but you have to flag the subpartition as COMPRESS first (in separate statement) because subpartition is not an object (partition is).

    COMPRESS statement really just sets a flag on the partition – it says “for all future operation treat this partition as compressed”. Then the next operation – in this case MOVE takes all data and physically moves them and because the subpartition is flagged as COMPRESS, it does the compression.

  3. Bharat says:

    Hi… Nyc explaination… just clearing my doubt…. So this implies that going forward all the the new data that is being inserted in subpartitions for which the partition was marked as compress (using alter table modify command ) the data would be kept inside that partition in compress mode.

    To illustrate more supposingly if i have a normal partition table with subpartitions
    PART.SUBPART
    with
    PARTITON A having SUBPARTITON A1,A2,A3
    PARTITON B having SUBPARTITON B1,B2,B3

    and I compress these partiions and SOME subpartitions using

    ALTER TABLE PART.SUBPART MODIFY PARTITION A COMPRESS;
    ALTER TABLE PART.SUBPART MOVE SUBPARTITION A1;
    ALTER TABLE PART.SUBPART MOVE SUBPARTITION A2;

    without moving subpartition A3

    than

    for all the new data inserts which qualifies the criteria of subparition A3
    will the data be in COMPRESSED state ?
    OR
    will the data be in UNCOMPRESSED state ?

  4. jiri says:

    That’s really interesting question. The short answer is “I don’t know”. Really I don’t.

    The easiest way to find out is to

    1. create two identical tables
    2. apply the alter statements on one
    3. insert data to both
    4. look at dba_segments tables

  5. Bharat says:

    Hey there !!! My assumptions were right.
    Once you change the Physical attribute of the partitions all the data being added in the future to the table inherits the property of the partition and hence the data is saved in compress mode in the subpartition.
    Regardless you use MOVE partition for the old data in subpartitions.

    Many thanks for replying though.

    Any idea on automating the compression of a partition table ?
    I’m in the middle of preparing a Stored Procedure for the same ?
    Preparing one for parition table seems OK
    BUT
    one having subparitions seems difficult considering the scenario I mentioned ?

  6. jiri says:

    Really guessing at this point (on a bus home typing on iPhone). If I understand it, you want to bulk compress? Not knowing much about your workflow or oracle version

    1. Are you inserting bulk? For example you could mark the partition as compress and keep inserting conventional way which does not compress if I am not wrong then do move once a while

    2. Maybe twisted solution, but you could always insert to separate table and then do partition swap

    Just thoughts

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: