Re: partitions and data retention

if you can set up sub-partitions by customer id or something like that, this is probably the best method.

I would recommend transportable tablespaces for offlining your old data as opposed to export. ITs faster. However, this makes your tablespaces/datafile mapping more complex. You will need to plan for more tablespaces so that one partition/sub-partition falls on a tablespace.

Its not that hard. You will just need to plan for it, get good naming convention on them, and have jobs to add new tablespaces when you add new partitions. You will probably want to track partition names (increment by some data in the partition name) in a table.

ran into a problem similiar to this myself lately... I have not implemented it. Just been thinking about it.
-------------- Original message --------------
From: "Merrill, Chris" <CMerrill_at_concordefs.com>

Hello All,
 I am looking for some advice.  

We have a handful of tables that are Range partitioned. This field is a varchar2 but we populate it with a date like '20061201'. We load around 20 million transactions a day. Since the application accesses the table by this field and is usually only for one or two days we decided to partition it on a daily basis to make use of partition pruning.  

The plan was after the partition was 6 months old we would export the partition and drop it. However I have been told that some customers may want the data up to three years to be online. The first option I can think of would be to use subpartitions. That way I can still truncate the sub-partitions that I need and keep the others.  

Any other options ?  

Each data partition is about 6 Gigs a day. 20 million rows. Will be accessed by by about 6000+ reports daily on the current partition key.  

Thanks
Chris Merrill
901 371 8000 ext 17873

The information in this message may be proprietary and/or confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify First Data immediately by replying to this message and deleting it from your computer.
--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 28 2006 - 09:23:19 CST