Skip navigation links

MySQL Forums :: Partitioning :: How to create partition on existing table?


Advanced Search

Re: How to create partition on existing table?
Posted by: Hartmut Holzgraefe ()
Date: May 25, 2009 03:34AM

jason zhang Wrote:
-------------------------------------------------------
> There is a table which already exists and has
> about 1 million records.
> How can I create partition on it?

you can use ALTER TABLE to add partitioning to the table,
keep in mind though that this will actually create the
new partitioned table first, then copy over all the
existing data, and finally drop the old unpartitioned
table. So this operation may take a while and will
temporarily use twice the disk space (which in the case
of InnoDB is not given back to the operating system ...)




mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
-> ;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE trb3 PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

--
Hartmut Holzgraefe, MySQL Regional Support Manager EMEA

Sun Microsystems GmbH, Sonnenallee 1, 85551 Kirchheim-Heimstetten
Amtsgericht Muenchen: HRB161028
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering

Options: ReplyQuote


Subject Views Written By Posted
How to create partition on existing table? 4363 jason zhang 05/25/2009 02:45AM
Re: How to create partition on existing table? 22054 Hartmut Holzgraefe 05/25/2009 03:34AM
Re: How to create partition on existing table? 6064 jason zhang 05/25/2009 07:02PM
Re: How to create partition on existing table? 5052 Ikrom Hotamov 07/02/2009 02:08AM


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.