Re: Partitioning question

I agree with Daniel.

If you are loading one day per day, then the obvious strategy for administrative reasons is one partition to one day.

You don't mention the version of Oracle, though, and this may affect the way in which you can query the data efficiently. Oracle's example of weekly partitions may be based on a need to minimise the cost of partition probing on the occasions when partition elimination cannot take place.

366 partitions is not a lot - although you have to multiply this up by the number of indexes plus one to calculate the real impact of (e.g.) dropping a partition. However, if you run queries where Oracle cannot see at parse/bind time that a specific, minimal, set of partitions is needed, than you will visit every partition. In some cases, and especially if you have rigged your indexes to address this particular issue, the cost of this probing will be relatively small; nevertheless, 365 redundant probes could be significantly more expensive than the basic cost of the query.

So - whatever you choose to do - find out the structure of the important queries, and check how effectively Oracle can do partition elimination for those queries, and how expensive it is when partition elimination does not take place.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Daniel Morgan wrote in message <3CFEEBC7.747EDE31_at_exesolutions.com>...


>SerGioGio wrote:

>

>> Hi !!

>>

>> A little question about Oracle's partitioning system...

>>

>> We have to load on one table about 500,000 rows each day, that is to

>> say approximately 2 GB.

>> We want also to perform a "rolling window" mechanism : we keep rows

>> until they become 1 year old, and then we want to delete them. So, in

>> the table, at any given time, there are only 365 days of loaded rows.

>> It looks a lot to me like a classical requirement ;)

>>

>> We are thinking of using one partition per day, but we are not aware

>> of the drawbacks. Oracle documentation says it would speed up process

>> (for exemple when deleting the 366 days old rows), but they also give

>> an example where they would not use a partition per day in a daily

>> load scenario but instead a partition per week (and then, update the

>> partition every day). It looks a bit more difficult to set up. They

>> are not very clear about why we shouldn't use one partition per day,

>> so I would like to know what are your opinions about that ?

>>

>> Thanks in advance !!

>>

>> SerGioGio

>

>My personal preference would be a partition per day I my intention was

>to have a one day floating window. Partition by week if I was going to

>have a one week window.

>

>Daniel Morgan

>