Re: DW partitioning and archiving

From: Tim Gorman <tim_at_evdbt.com>
Date: Fri, 27 May 2011 06:47:15 -0600
Message-ID: <4DDF9D53.3000404_at_evdbt.com>

Michael,

If partitioning was implemented without regard for archival/purge or tiered storage, then it is most probably not feasible using partition management.

I like to think about partitioning as a "gun with two bullets", which is one way of looking how one chooses the partition key column and the sub-partition key column.  If you think about facilitating data loading, archival/purge or tiered storage, and partition-pruning during query as three targets, you have two bullets to use in nailing those three targets.  Sometimes one bullet can hit all three targets, sometimes two of them, sometimes only one.  It does not sound like sub-partitioning has been implemented, by the way, so at present you've one bullet in the holster, unused and unusable.

To make a long story short, I advocate range partitioning both FACT and DIM on a DATE column first, in order to facilitate data loading and archival/purge or tiered storage.  Then, I tend to sub-partition both FACT and DIM on any other datatype column next, usually to faciliate partition-pruning during queries, but possibly to facilitate other considerations such as mass-integration of future data (i.e. future data warehouse consolidation, etc).  Sub-partitioning can be very free-format, very creative, but range partitioning on a DATE column first is pretty much a requirement in order to reap the greatest benefits from partitioning.  Data loading, archival/purge or tiered storage, and partition-pruning are sometimes all possible at once with the range partitioning by DATE, but sometimes you have to choose only two of the three, as with anything else in engineering.

I have a white paper at "http://www.evdbt.com/TGorman%20TD2009%20DWScale.doc" and a presentation at "http://www.evdbt.com/OOW09%20DWScaling%20TGorman%2020091013.ppt" on these topics.

Hope this helps...

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => 13087 West Cedar Drive #225, Lakewood CO 80228
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

On 5/25/2011 5:24 PM, Michael Dinh wrote:

Please share experience and/or information on archiving for data warehouse.

 

If DIM and FACT are already partitioned on one column, how does archiving work if based on another column or is it even feasible.

 

TIA

 

Michael Dinh

 

NOTICE OF CONFIDENTIALITY - This material is intended for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable laws.  BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE.  If the reader of this email (and attachments) is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. Please notify the sender of the error and delete the e-mail you received. Thank you.

-- http://www.freelists.org/webpage/oracle-l Received on Fri May 27 2011 - 07:47:15 CDT