Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Table partitioning problem
Notes in-line
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:4080db90$0$12481$afc38c87_at_news.optusnet.com.au...Received on Sat Apr 17 2004 - 09:40:03 CDT
>
> >>partition test_30 values (30),
> >>partition test_31 values (31)
> >>);
> >>
> >>I get syntax errors on the PARTITION BY RANGE line. It seems to expect
> >>*only* a fieldname there.
> >>
>
> I wouldn't do that for two reasons: one, list partitioning is fairly
> new, whereas range partitioning isn't (bugs?).
Not all that new - 9.0 was the first release, I believe - it's just that time passes faster as we grow older. I'd also guess that list partitioning is a relatively minor evolution from hash partitioning, and therefore likely to be fairly clear of 'new' bugs.
>
And two, list partitions
> cannot be hash sub-partitioned, whereas range partitions can. The OP may
> never need hash sub-partitioning, it is true.... but then again, he
> might. Range partitioning will do what he definitely needs to do, and
> gives him flexibility in the future should business requirements need it.
>
On the other hand, range partitioning will allow errors to occur with illegal values permitted, unless there is also a check constraint to enforce exactly the correct set of values. Note that he/she wants exactly 31 partitions to cover the 31 possible values of day component in a date. It's not a natural range-partition. In passing, for the benefit of the OP, the main problem with the example is that the partitioning column has to be defined as a real column in the table. It would be possible to create an extra column populated by a trigger (which would be slow on bulk loading) but the column HAS to exist.