Wednesday, November 11, 2015

[Exadata] How to partition a Table yearly and monthly with respect to DATE T

Hi again,

In this post i am trying to partition a big table in Oracle 11gR2 database in Exadata like this: rows which are earlier than a DATE value (for example Year 2015) must be separeated yearly and rows which are newer must be partitioned by montly. 


For instance:

1,'row1','10/03/2010               to partition              Part_Before_2011
5,'row12','10/08/2010              to partition              Part_Before_2011
3,'row56','01/11/2013              to partition              Part_Before_2014
78,'row4','01/03/2015'             to partition              Part_After_2015
327,'row114','03/06/2015'          to partition              Part_After_20156




Keep in mind that Interval Partitioning helps us to create dynamic partitions so we don't need to do partition maintenance while inserting. But you should give default partititons manually in order to gather the rows before DATE T yearly. 

So i create my table like this.

CREATE TABLE my_big_table_NEW (
...
mydate DATE,
...
)
PARTITION BY RANGE (mydate)   
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )   
(
PARTITION p2009 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')) COMPRESS FOR ARCHIVE HIGH,    
PARTITION p2010 VALUES LESS THAN (TO_DATE('01/01/2011','DD/MM/YYYY')) COMPRESS FOR ARCHIVE HIGH,    
PARTITION p2011 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY')) COMPRESS FOR ARCHIVE HIGH,    
PARTITION p2012 VALUES LESS THAN (TO_DATE('01/01/2013','DD/MM/YYYY')) COMPRESS FOR ARCHIVE HIGH,    
PARTITION p2013 VALUES LESS THAN (TO_DATE('01/01/2014','DD/MM/YYYY')) COMPRESS FOR ARCHIVE HIGH,    
PARTITION p20014 VALUES LESS THAN (TO_DATE('01/01/2015','DD/MM/YYYY')) COMPRESS FOR ARCHIVE HIGH,    
PARTITION p200151 VALUES LESS THAN (TO_DATE('01/02/2015','DD/MM/YYYY')) COMPRESS FOR QUERY HIGH
);


As you can see some parititons are rarely accessed (before 2015, yearly), so i compressed them with archive high option. And the partitions which are mostly queried compressed with query high option.

What I did this work because I faced a issue that some of my big tables in Exadata are not partitioned (very old table ) and table has 382gb size with more than 14B rows,  and queries do FULL table scan on table. It is terrible :/

Once you create your new partitioned tables, be careful to load data with APPEND option. Or you  will cry :) You can load data with following in Exadata.

SQL> Insert /*+ APPEND parallel(32) */  my_big_table_NEW 
      select /*+ PARALLEL(32) */ * from my_big_table;
SQL> commit;
SQL> Alter table my_big_table rename to my_big_table_old;
SQL> Alter table my_big_table_NEW rename to my_big_table;

After i transformed my table queries gained huge performance. Here are the results.

In my AWR reports for an whole day, the query running against my big table shows up like this.(last line)




But after passing to partition type, i couldn't see it top SQLs. So i got a picture for it. Note that, The queries covers near-same date range.




Ok, that is all
Thanks for reading.
Enjoy & share.

Source:
http://www.oracle.com/technetwork/articles/sql/11g-partitioning-084209.html 








No comments :

Post a Comment