Re: Daily partitioning
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: s.cislaghi@xxxxxxxxx
- Date: Fri, 14 May 2010 10:30:05 -0700
Let's take 10 steps back ... What is your goal in using partitioning
(e.g. what problem are you solving or attempting to solve)? Doing
weekday based partition granules doesn't seem to yield much benefit.
Best case, when a query wants a given day, it scans 1/7 of the total
data (assuming table/partition scan), but if you have 6 weeks/42 days,
then it scans 5x more than it needs (there are 6 days in each
partition and you want 1 days worth). When data is purged, it still
must be done via a delete; with daily partitions, a drop partition can
be used.
There are no rules about how big/small a partition should be but it
should make sense (the pros outweigh the cons) -- in this case the
ability leverage partitioning for ILM as well as data elimination.
I'd recommend daily partitions which can be range/list/interval
depending on what your date column looks like.
Your comments suggest the ETL does the purging, so the logic would
have to change to add a new partition and drop the old partition.
Slightly more characters in the command than a simple DELETE, but
surely not rocket science. There are a million shops out there that
use this exact methodology.
On Fri, May 14, 2010 at 8:26 AM, Stefano Cislaghi <s.cislaghi@xxxxxxxxx> wrote:
> 1. Batch load every nigh
> 2. The whole table may be about 6Gb
> 3. DSS query using date field
> 4. 11gR2
> I do not want to use interval partitioning because I want to divide
> partition in more tbs (interval partitioning will not store with logic
> partition in tbs) and also data are pruned by the batch job and so I do not
> want to have a maintenance to remove old ununsed empty partitions. I'm agree
> with Leyi that 40 partitions are not big, but IMHO 40 partition containing
> 100 mb each one is not really nice.
> 11gR2 permits to use partitioning on virtual column, and so I can create a
> virtual column that retrieve day of week and partition with this.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
