Re: Daily partitioning
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: s.cislaghi@xxxxxxxxx
- Date: Fri, 14 May 2010 07:12:15 -0700
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).
On Fri, May 14, 2010 at 3:17 AM, Niall Litchfield
<niall.litchfield@xxxxxxxxx> wrote:
> You sound as if you *might* be describing a situation suited for 11g's
> interval partitioning, but it's rather difficult to tell. It's also
> extremely likely that your maintenance should not be DELETE but ALTER TABLE
> .. DROP PARTITION
> On Fri, May 14, 2010 at 11:05 AM, Stefano Cislaghi <s.cislaghi@xxxxxxxxx>
> wrote:
>> This table will retain data for last 40 days from sysdate, i.e. delete *
>> from mytable where date < sysdate-40
>> Managing a partition for each day is too big ... 31 partitions.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
