Partitions with different attributes?



This past week the topic of archiving came up - the thing that everyone talks about but no-one does....

One of the topics that came up was that old partitions could be exchanged out of the main table and stored away from the main table to help in certain performance cases - we would still like access to them and the ability to switch it back in if required - we do not want it written off to some tape that we may never be able to retrieve from again.

We discussed compression and if it was possible to have a partition with different compression attributes to the main table definition - i wasn't sure if this was allowed or not so i tried out a simple test case.

I created a simple partitioned table and specified that one of the partitions was compress for oltp - see the syntax below

    CREATE TABLE "SCHEMA"."TEST5"
  TABLESPACE "TBS1"
  PARTITION BY RANGE ("OBJECT_ID")
(PARTITION x1 VALUES LESS THAN (50000)  TABLESPACE "TBS1" ,
PARTITION x2 VALUES LESS THAN (100000)  TABLESPACE "TBS1"  compress for oltp,
PARTITION x3 VALUES LESS THAN (150000)  TABLESPACE "TBS1" ,
PARTITION x4 VALUES LESS THAN (200000)  TABLESPACE "TBS1" ,
PARTITION x5 VALUES LESS THAN (250000)  TABLESPACE "TBS1" ,
PARTITION x6 VALUES LESS THAN (300000)  TABLESPACE "TBS1" ,
PARTITION x7 VALUES LESS THAN (350000)  TABLESPACE "TBS1" ,
PARTITION x8 VALUES LESS THAN (MAXVALUE)  TABLESPACE "TBS1" )
as select * from DBA_OBJECTS where object_id is not null;

Querying that back we see its taken it fine

SQL>  select table_name,COMPRESSION,COMPRESS_FOR,partition_name from dba_tab_partitions where table_name='TEST5'
  2  /

TABLE_NAME                     COMPRESSION                      COMPRESS_FOR                                     PARTITION_NAME
------------------------------ -------------------------------- ------------------------------------------------ ------------------------------
TEST5                          DISABLED                                                                          X8
TEST5                          DISABLED                                                                          X7
TEST5                          DISABLED                                                                          X6
TEST5                          DISABLED                                                                          X5
TEST5                          DISABLED                                                                          X4
TEST5                          DISABLED                                                                          X3
TEST5                          ENABLED                          OLTP                                             X2
TEST5                          DISABLED                                                                          X1

I could have stopped at this point and assumed all was OK but i decided to do a quick test with partition exchange to check that was OK as that would be our main switch in/out method.

First i create a new table that i'm going to switch with

create table "SCHEMA"."TEST6"  TABLESPACE "TBS1" 
as select * from DBA_OBJECTS where 1=0;

Then i do the switch - so partition x7 becomes the table TEST6

ALTER TABLE "SCHEMA"."TEST5"
  EXCHANGE PARTITION x7 WITH TABLE "SCHEMA"."TEST6"
  INCLUDING INDEXES
  WITHOUT VALIDATION;

To prove that we can see the row count

SQL> select count(*) from "SCHEMA"."TEST6";

  COUNT(*)
----------
      4122

Lets check how many blocks it uses

SQL> exec dbms_stats.gather_table_stats('SCHEMA','TEST6');

PL/SQL procedure successfully completed.

SQL> select blocks from dba_tables where table_name='TEST6';

    BLOCKS
----------
        88

Ok now we activate compression on it

SQL> alter table "SCHEMA"."TEST6" compress for oltp;

Table altered.

And move the table to force the compression to be done - just activating compression does not touch existing rows

SQL> alter table "SCHEMA"."TEST6" move;

Table altered.

Now we check the size again

SQL> exec dbms_stats.gather_table_stats('SCHEMA','TEST6');

PL/SQL procedure successfully completed.

SQL> select blocks from dba_tables where table_name='TEST6';

    BLOCKS
----------
        48

And it's roughly halved in this simple example

Now we switch back in the newly partitioned table

ALTER TABLE "SCHEMA"."TEST5"
  EXCHANGE PARTITION x7 WITH TABLE "SCHEMA"."TEST6"
  INCLUDING INDEXES
  WITHOUT VALIDATION;

We now check what the partitions look like and we can see both my original X2 and now X7 are compressed where everything else is not - so this seems to work fine.

SQL> select table_name,COMPRESSION,COMPRESS_FOR,partition_name from dba_tab_partitions where table_name='TEST5';

TABLE_NAME                     COMPRESSION                      COMPRESS_FOR                                     PARTITION_NAME
------------------------------ -------------------------------- ------------------------------------------------ ------------------------------
TEST5                          DISABLED                                                                          X8
TEST5                          ENABLED                          OLTP                                             X7
TEST5                          DISABLED                                                                          X6
TEST5                          DISABLED                                                                          X5
TEST5                          DISABLED                                                                          X4
TEST5                          DISABLED                                                                          X3
TEST5                          ENABLED                          OLTP                                             X2
TEST5                          DISABLED                                                                          X1

8 rows selected.

Just as some background we did have everything in compression mode but it killed the batch insert performance so we had to take it all back off again.

There is new stuff in 12 to automate a lot of this but for us on 11.2 this might be a suitable approach as it moves some of the data away and could save us a huge amount of space.




0 comments:

Post a Comment