Compressed partitions are not compressed tables

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  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  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.

7 comments

Noons says:

August 27, 2013 at 9:21 am

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.

Anand says:

October 3, 2014 at 6:34 am

Very helpful. Thanks.

Peter says:

November 27, 2016 at 1:34 am

Do you need perform 2 steps when compressing the partitions? You did a alter table modify partition compress, the move partition – can you not simply do that in one step with alter table move partition compress?

Pingback: What you "liked" last year… | Learning is not a spectator sport
Pingback: 2017–what grabbed your attention | Learning is not a spectator sport
ptrikha15 says:

November 7, 2019 at 7:52 pm

That is superbly shown!! But where would we have such a use case of merging partitions?

1. Connor McDonald says:
  
  November 20, 2019 at 2:16 pm
  
  Often to keep the partitions down to a manageable number, or for other reasons, eg https://connor-mcdonald.com/2018/07/25/hyper-partitioned-index-avoidance-thingamajig/