MySQL Forums
Forum List  »  Partitioning

Why the limit that every partition must contain the same number of subpartitions ?
Posted by: Matthias Leich
Date: January 27, 2006 10:34AM

Hi Mikael,

I just detected during test development that I cannot have different number of subpartitions
per partition. I got the error message
ERROR HY000: Trying to Add partition(s) with wrong number of subpartitions
on ALTER TABLE ADD PARTITION .....

Is there any special reason for this limitation ?

I assume that there is a high probability, that many tables with different number
of subpartitions per partition will not show the properties expected by the table creator.
Especially if disks have the same performance, the access frequency per record
does not significant differ between the records, ....

Couldn't there be "exotic" situations where this limitation becomes a disadvantage ?
I hope my example is not too academic or far away from reality :-)
Generic example:
- Huge table PARTITION BY RANGE -- SUBPARTITION BY HASH or KEY
- Two subpartitions per partition , one disk per subpartition
- Every day many rows with higher PRIMARY KEY values like the existing one
will be inserted. Table growth per day is constant.
The upper value limit of the partitions is nearly reached and the DBA decides
to add a partition. He installs an additional new disk which is twice as big as
the other ones. So he can put the whole stuff of the new partition on this disk.
But why shoud he use two subpartitions (-> two fat files) instead of one
partition without subpartitions (one fat file) ? I guess the second variant is
faster when SELECTING via KEY range and physical order of records =
logical order with ascending KEY.

Of course a rearrangement of the partitioning scheme of the table
-- redefine partitions and do not use subpartitions - by
ALTER TABLE <whatever> is an option, but I fear that such operations on
huge tables might need hours and maybe huge temporary disc space.

Thanks in advance

Regards,

Matthias

--
Matthias Leich, Software Developer
MySQL AB, www.mysql.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Why the limit that every partition must contain the same number of subpartitions ?
3997
January 27, 2006 10:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.