Re: Partitioning tables

There are several variables to consider.

You might be better off with one partition per week as this could allow increased (and indexed) parallelism if all your queries cover a small but mobile range of weeks.

This might give you too many partitions, of course, and very small partitions, which might have a bit of nuisance value.

Ideally you do not want to have to split a partition that contains data. So an alternative to one partition per week is to pick a physical partition size and decide on a fixed number of weeks to go into it, then pre-create a few partitions ahead all the time.

You could, however, max the top partition terminate at MAXVALUE, so that no data is ever rejected with 'value beyond partition limit'; however you may then have to split the partition at a new week boundary, and this could take some time.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Jenda Krynicky wrote in message <1103_942941750_at_prague_main>...
>I have a huge table (OK, OK ... most of you have seen bigger ;-) and would
like to partition it (I think it
>would help performance).
>
>One of the fields in the table is a "weeknumber" (not number within a year,
but number of weeks
>since some date) and I think since all queries contain
>
> WHERE AGBWEEK >= whatever AND AGBWEEK <=something ...
>
>this would be the best field to partition the table on. Right?
>
>I thought I could create a partition for every N weeks.
>
>But we get new data every week so the upper bound - the maximum week
number - changes.
>
>So ... what should I do?
>Should I "prealocate" several partitions for upcoming weeks?
>And then repartition the table after the last partition gets too big?
>
>Or is is possible to split the last partition when necessary ?
>Or instruct Oracle to create a new partition every time the partitioning
field increases above a limit?
>
>Thanks and please forgive my misunderstandings. (I'm sure there are many.)
>Jenda
>http://Jenda.Krynicky.cz
>
Received on Thu Nov 18 1999 - 12:46:53 CST