I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have some partitioned tables for data older than that. Have not chosen mysql or sql server express as not sure which one has the desired capabilities.
I have read some examples on how partitioning on ranges works as seen below:
CREATE TABLE SAMPLE_ORDERS
(
ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER
)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-2010’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2010’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2010’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2011’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2011’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2011’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2011’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2012’, ‘DD-MON-YYYY’)
);
Read more at http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/1/#YCTAmfR0hkvo1at0.99
But this seems to be more for partitioning older data. What happens when it reaches newer data? Will it put the new data into a single partition?
I would rather have a small amount of data in the new table and dump all the old information.
Do I just need to create multiple partitions up to 2020, and keep manually adding them as the project runs?
Is there another way to do this?
I know I could achieve what I need by replicating all the data into another table, and cleaning up the old data every night, however I have had issues with this before and was wondering if partitioning would do a better job?
Best Answer
In MySQL you have to add a new partition on your CREATE TABLE statement
as an alternative yuo can ALTER your table adding the new MAXVALUE partition
From the official documentation
In future you can use an ALTER TABLE for managing and reorganaizing the new data greater then your "last working partition(LESS THAN TO_DATE(‘01-JAN-2012’, ‘DD-MON-YYYY’) )" inserted in the MAXVALUE partition.
Yuo can find more details on:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-range.html
http://dev.mysql.com/doc/refman/5.1/en/partitioning-management-range-list.html
Hope this help
Cristian