RE: Daily partitioning
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ivanrs79@xxxxxxxxx>, "'Oracle L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 May 2010 11:44:07 -0400
I'm pretty sure Greg was noting that if you wanted to keep with the day at a
time rhythym, then daily deletes would be needed. If a weekly bulge can be
allowed a partition swap or truncate can still be done.
Run, don't walk, to your nearest browser and search up "Tim Gorman" "Scaling
to Infinity" and figure out which might be a recent version and read it
thoroughly.
Then, if you cannot prevent your data integrator from deleting, at least get
them to just mark the rows for deletion. A good scheme is for the row to be
born with a column logically named "NOT_DELETED_YET" filled in with a not
null value. Then the integrator's software punches that to NULL instead of
deleting the row. You can check for any remaining not null values before you
swap an empty partition on top of the partition you're removing. If there is
a priority order for the deletions you can use a date, timestamp, or number.
Otherwise any old byte will do.
Or something like that. Most likely your exact situation has additional
complications to consider. But don't quickly give up on the quest to
eliminate logging operations. Usually it can be done without too much
complication and it is essential to open the door to scaling reasonably.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ivan Ricardo Schuster
Sent: Friday, May 14, 2010 10:41 AM
To: Oracle L
Subject: Re: Daily partitioning
> addition 7 weekday partitions mean you have to *delete* old data, not
> simply drop the given day's partition. That means a logging operation
Not really, you can truncate that partition, that is not a logging
operation.
11g has the "partition by function", that you can define a function
and partition your table using the result of that.
Something like "mod(to_char(sysdate,'J'),41)" car partition your table.
On 14 May 2010 11:12, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
> 40 daily partitions is doesn't seem like that much work. It can
> easily be automated if using range, and interval (which is really
> automatic range). It will also yield the best data elimination if the
> data access is by day or days. Other partition granule sizes will
> obviously include much more data -- e.g. 7 weekday partitions would
> mean that for any given day, there would be 5/6 (assuming 42 days, 6
> days per weekday partition) more data than you are looking for. In
> addition 7 weekday partitions mean you have to *delete* old data, not
> simply drop the given day's partition. That means a logging operation
> vs a nologging operation and likely minutes/hours vs seconds
> (depending on your data size, of course).
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
