Help with partitioning an existing table
Posted by: Ravi Malghan
Date: December 05, 2012 10:53AM

I am a newbie to mysql db. I have a table that I have partitioned by a datetime field
create table status (
        SERVERSERIAL    NUMERIC(16) NOT NULL,
        SERVERNAME      VARCHAR(64) NOT NULL,
        FIRSTOCCURRENCE DATETIME NULL,
        CUSTOMER VARCHAR(64) NULL,
        <..many other fields>
        PRIMARY KEY ( SERVERSERIAL, SERVERNAME,FIRSTOCCURRENCE )
)
PARTITION BY RANGE COLUMNS (FIRSTOCCURRENCE)
(
        PARTITION p2012_3 values less than ('2012-09-30'),
        PARTITION p2012_4 values less than ('2012-12-31'),
        PARTITION p2013_1 values less than ('2013-03-31'),
        PARTITION p2013_2 values less than ('2013-06-30'),
        PARTITION p2013_3 values less than ('2013-09-30'),
        PARTITION p2013_4 values less than ('2013-12-31'),
        PARTITION p2014_1 values less than ('2014-03-31'),
        PARTITION p2014_2 values less than ('2014-06-30'),
        PARTITION p2014_3 values less than ('2014-09-30')
)

The table currently has 3Mil rows. I want to partition it further by customer since most of my queries also have customer name in the where clause. Have a few questions

a. I think I need to add the Customer field to the primary key. If so, do I need to suspend all writes to the database when I run the alter command?
b. can someone help with the alter statement for adding customer to partitioning in addition to FIRSTOCCURRENCE or some pointers?

Thanks
Ravi

Options: ReplyQuote


Subject
Written By
Posted
Help with partitioning an existing table
December 05, 2012 10:53AM


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.