Re: partitions and data retention
- From: ryan_gaffuri@xxxxxxxxxxx
- To: CMerrill@xxxxxxxxxxxxxx, "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 Nov 2006 15:23:19 +0000
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@xxxxxxxxxxxxxx>
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.
Other related posts:
