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.
Comments
Post a Comment