partitions and data retention

From: Merrill, Chris <CMerrill_at_concordefs.com>
Date: Tue, 28 Nov 2006 09:32:19 -0500
Message-ID: <4F4897231C9D52499C811CE98150591015AC51@memex1.corp.cefs.int>

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 - 08:32:19 CST