Learning is not a spectator sport

August 26, 2013

Compressed partitions are not compressed tables

Filed under: Uncategorized — connormcdonald @ 8:41 pm

So…you have got a big table and you’re lucky enough to have a partitioning license.  So far, so good.

Then you read about using compression to make your older, static data faster and smaller and you thought … "Cool!"

Let’s see that in action – first here is my uncompressed partition table

SQL> create table T ( x date, y int, z varchar2(50) )
  2  PARTITION BY RANGE (x) INTERVAL (INTERVAL '60' DAY)
  3    (PARTITION p1 VALUES LESS THAN (TO_DATE('01/01/2012','dd/mm/yyyy')  )
  4   )
  5  /

Table created.

SQL> insert /*+ APPEND */ into T
  2  select sysdate - 1000+rownum/100, trunc(rownum/1000), rpad('x',50)
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             DISABLED   101.838046
SYS_P241                       DISABLED           80
SYS_P242                       DISABLED           80
SYS_P243                       DISABLED           80
SYS_P244                       DISABLED           80
SYS_P245                       DISABLED           80
SYS_P246                       DISABLED           80
SYS_P247                       DISABLED           80
SYS_P248                       DISABLED           80
SYS_P249                       DISABLED           80
SYS_P250                       DISABLED           80
SYS_P251                       DISABLED         17.5

and now I’ll compress all of the partitions

SQL> begin
  2  for i in (
  3  select partition_name,  compression
  4  from dba_tab_partitions
  5  where table_name = 'T' )
  6  loop
  7    execute immediate 'alter table t modify partition '||i.partition_name||' compress';
  8    execute immediate 'alter table t move partition '||i.partition_name;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P241                       ENABLED    206.896552
SYS_P242                       ENABLED    206.896552
SYS_P243                       ENABLED    206.896552
SYS_P244                       ENABLED    206.896552
SYS_P245                       ENABLED    206.896552
SYS_P246                       ENABLED    206.896552
SYS_P247                       ENABLED    206.896552
SYS_P248                       ENABLED    206.896552
SYS_P249                       ENABLED    206.896552
SYS_P250                       ENABLED    206.896552
SYS_P251                       ENABLED    20.2631579

12 rows selected.

so I give myself a nice big pat on the back and sit and bask in the glow of a job well done :-)

But wait….I then want to merge a couple of those partitions…Let’s see what happens

SQL> col x new_value p1
SQL> select partition_name from user_tab_partitions
  2  where partition_position = 4;

PARTITION_NAME
------------------------------
SYS_P243

SQL> col x new_value p2
SQL> select partition_name from user_tab_partitions
  2  where partition_position = 5;

PARTITION_NAME
------------------------------
SYS_P244

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR;

Table altered.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T';

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
SYS_P251                       ENABLED    20.2631579
SYS_P248                       ENABLED    206.896552
SYS_P245                       ENABLED    206.896552
NEW_PAR                        DISABLED   91.6030534
P1                             ENABLED    450.170455
SYS_P242                       ENABLED    206.896552
SYS_P246                       ENABLED    206.896552
SYS_P241                       ENABLED    206.896552
SYS_P247                       ENABLED    206.896552
SYS_P250                       ENABLED    206.896552
SYS_P249                       ENABLED    206.896552

11 rows selected.

Uh oh….the act of merging the two compressed partitions has resulted in an uncompressed partition.  Now I would be forced to do an "alter table move partition" if I wanted to get those compression benefits back.  That’s not fun :-(

Now let’s repeat the exercise, but this time…I knew in advance that I might want to compress that table sometime in the future

SQL> drop table T purge;

Table dropped.

SQL> create table T ( x date, y int, z varchar2(50) )
  2  PARTITION BY RANGE (x) INTERVAL (INTERVAL '60' DAY)
  3    (PARTITION p1 VALUES LESS THAN (TO_DATE('01/01/2012','dd/mm/yyyy')  )
  4   )
  5   compress
  6  /

Table created.

SQL> insert /*+ APPEND */ into T
  2  select sysdate - 1000+rownum/100, trunc(rownum/1000), rpad('x',50)
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P261                       ENABLED    206.896552
SYS_P262                       ENABLED    206.896552
SYS_P263                       ENABLED    206.896552
SYS_P264                       ENABLED    206.896552
SYS_P265                       ENABLED    206.896552
SYS_P266                       ENABLED    206.896552
SYS_P267                       ENABLED    206.896552
SYS_P268                       ENABLED    206.896552
SYS_P269                       ENABLED    206.896552
SYS_P270                       ENABLED    206.896552
SYS_P271                       ENABLED    20.2631579

12 rows selected.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> col x new_value p1
SQL> select partition_name x from user_tab_partitions
  2  where partition_position = 4;

X
--------------------------------------------------------------------------------------------------------------------------------
SYS_P263

SQL> col x new_value p2
SQL> select partition_name x from user_tab_partitions
  2  where partition_position = 5;

X
--------------------------------------------------------------------------------------------------------------------------------
SYS_P264

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR;

Table altered.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T';

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P271                       ENABLED    20.2631579
SYS_P265                       ENABLED    206.896552
SYS_P266                       ENABLED    206.896552
SYS_P270                       ENABLED    206.896552
SYS_P267                       ENABLED    206.896552
SYS_P269                       ENABLED    206.896552
SYS_P262                       ENABLED    206.896552
SYS_P268                       ENABLED    206.896552
NEW_PAR                        ENABLED           300
SYS_P261                       ENABLED    206.896552

11 rows selected.

Well look at that !

It’s at this point, you’re probably thinking that this is a bug, or that you get "one shot" at this otherwise you’re doomed to not get compression.

But that’s not the case – it’s just a nuance of the way Oracle is interpreting your requests.  The MERGE PARTITION command is in effect creating a NEW partition, hence the new partition takes on the default compression clause at table level.  So, in the uncompressed example above, if I simply did

SQL> alter table T compress;

Table altered.

before I merge the partitions, then the resulting partition is indeed compressed.

Alternatively, although I can’t find an obvious reference in the documentation that this is permitted, the following also works:

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR compress;

Table altered.

So to wrap up – be careful when manipulating partitions when dealing with compression – you might just end up with some uncompressed ones.

About these ads

2 Comments »

  1. Wow! Thanks for that! I had no idea merge partition took its default compression from the table def, although I’ve used it before with compressed tables.

    Comment by Noons — August 27, 2013 @ 9:21 am

  2. Very helpful. Thanks.

    Comment by Anand — October 3, 2014 @ 6:34 am


RSS feed for comments on this post. TrackBack URI

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

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: