Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Table partitioning problem

Re: Table partitioning problem

From: Howard J. Rogers <>
Date: Sun, 18 Apr 2004 13:04:00 +1000
Message-ID: <4081f023$0$6018$>

>Notes in-line


I just thought I'd mention, having just stumbled across the problem, that when you top-post and include the "--" to inidcate the start of your signature, Mozilla Thunderbird decides that everything after that point should not be included in the reply. Which makes replying a tad difficult!

>> 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

No need to believe. List partitioning was indeed new in 9i Release 1, though without the 'default partition' that only made an appearance in 9i Release 2. And that was exactly my point: things weren't quite right in 9i Release 1, got fixed in 9i Release 2, and range partitioning has been around since 8.0. I know which one I would trust to be more , er, stable.

>- 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'

Hmmm. Not my recollection of Metalink at all. But whatever...

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.

I dunno about that! It's not a natural list partition, either, if you tend to take the view that self-declared lists are there to impose some grouping structure on data which Oracle wouldn't otherwise be aware of (such that "New York" and "Vermont" both happen to be in the top right-hand corner of the US, whilst "Arizona" and "Nevada" are way off to the left and the south). Dates don't fall into that category.

As to the permissibility of illegal values: not having a maxvalues takes care of inadvertently inserting a date of the 32nd January. I would suggest, too, that a check constraint is exactly what's called for to prevent insertions of 0th January. Partitioning should, IMHO, be taking care of the *physical* storage of data, not performing logical checks on it, which is what constaints were invented to do.

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.

Well, apart from the obvious syntactical howlers, that's an important point too.

HJR Received on Sat Apr 17 2004 - 22:04:00 CDT

Original text of this message