Range partition overlap

[Justin]

What is the syntax to overlap partitions? Lets assume I want a year
split amount 4 months (3 partitions for a year) and later adding an
additional set of partitions for every 6 months. Plus giving us the
option to detach old partitions.

For example:
CREATE TABLE orders(id INT, shipdate DATE, …)
PARTITION BY RANGE(shipdate)
(
PARTITION m12y05 STARTING MINVALUE,
PARTITION m4y06 STARTING '1/1/2006',
PARTITION m8y06 STARTING '5/1/2006',
PARTITION m12y06 STARTING '9/1/2006'
ENDING ‘12/31/2006'
)

=============== =========

-- Detach old data
detach partition m12y05

=============== =============

-- Later applying additional partitions

Alter table orders
Add Partition
(
Partition s6y06 starting '1/1/2006',
Partition s12y06 starting '7/1/2006' ending '12/31/2006'
)

[Serge Rielau]

Re: Range partition overlap

Justin,

I do not understand your question.
Partitions do not overlap. That's an oxymoron.

Your details request on the other hand seems to not imply that question
either. To the bets of my knowledge I don't think anything prevents you
from extending a range partitioned any which way you like....

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Justin]

Re: Range partition overlap


Hi Serge,

I think you answered my question. But, I do not understand your point
in extending partitions.

If partitions cannot overlap, that is fine. Is it possible to add a
partition?

For example, I have two years of data (2007 and 2008). The partition
is set-up by year. What is the default when 2009 comes to the table?
Is this the process of extending a partition?

In the process, I would like to detach 2007 data to slower disk
(different tablespace).


On Aug 19, 5:28 pm, Serge Rielau <srie...@ca.ibm .comwrote:

Justin,
>
I do not understand your question.
Partitions do not overlap. That's an oxymoron.
>
Your details request on the other hand seems to not imply that question
either. To the bets of my knowledge I don't think anything prevents you
from extending a range partitioned any which way you like....
>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Serge Rielau]

Re: Range partition overlap

Justin wrote:

Hi Serge,
>
I think you answered my question. But, I do not understand your point
in extending partitions.
>
If partitions cannot overlap, that is fine. Is it possible to add a
partition?
>
For example, I have two years of data (2007 and 2008). The partition
is set-up by year. What is the default when 2009 comes to the table?
Is this the process of extending a partition?
>
In the process, I would like to detach 2007 data to slower disk
(different tablespace).
>
>
On Aug 19, 5:28 pm, Serge Rielau <srie...@ca.ibm .comwrote:

>Justin,
>>
>I do not understand your question.
>Partitions do not overlap. That's an oxymoron.
>>
>Your details request on the other hand seems to not imply that question
>either. To the bets of my knowledge I don't think anything prevents you
>from extending a range partitioned any which way you like....

Yes, you can ATTACH new partitions. Take a look at the ALTER TABLE
statement.

Cheers
Serge


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab