By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,219 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,219 IT Pros & Developers. It's quick & easy.

Range partition overlap

P: n/a
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'
)

Aug 18 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Aug 19 '08 #2

P: n/a

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
Aug 25 '08 #3

P: n/a
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
Aug 26 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.