Learning is not a spectator sport

Compressed partitions are not compressed tables

3–5 minutes

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.

9 responses to “Compressed partitions are not compressed tables”

  1. Noons Avatar
    Noons

    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.

    Reply
  2. Anand Avatar
    Anand

    Very helpful. Thanks.

    Reply
  3. Peter Avatar
    Peter

    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?

    Reply
  4. What you "liked" last year… | Learning is not a spectator sport Avatar
    What you "liked" last year… | Learning is not a spectator sport

    […] https://connormcdonald.wordpress.com/2013/08/26/compressed-partitions-are-not-compressed-tables/ […]

    Reply
  5. 2017–what grabbed your attention | Learning is not a spectator sport Avatar
    2017–what grabbed your attention | Learning is not a spectator sport

    […] Compressed partitions are not compressed tables […]

    Reply
  6. ptrikha15 Avatar
    ptrikha15

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

    Reply
    1. Connor McDonald Avatar
      Connor McDonald

      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/

      Reply
  7. Anuraag Avatar
    Anuraag

    Hi Connor,

    Great explanation. As you did mention about partitioning licenses, If I am not wrong compressing partition or table will require additional licenses for “Advance Compression option”.

    Thanks,

    Reply
    1. Connor McDonald Avatar
      Connor McDonald

      Depends on the compression you want to to. Basic compression does not need the ACO.

      Reply

Got some thoughts? Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trending