Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Aug 29 '12 at 9:21

This question came from our site for professional and enthusiast programmers.

    
How about writing a script with good logic to partition keys involved and scheduling for every quarter/calendar/fiscal on the partitioned object such that it alters the schemes and functions for the object(s) using split option? –  DBAuser Aug 30 '12 at 13:44
    
Perhaps you could write a script with logic to alter the schemes and functions for the object(s) using split option and scheduling this script to occur every quarter/calendar/fiscal for the partitioned object? –  DBAuser Aug 30 '12 at 15:32

2 Answers 2

up vote 2 down vote accepted

In MySQL you have to add a new partition on your CREATE TABLE statement

PARTITION <partitionname> VALUES LESS THAN MAXVALUE

as an alternative yuo can ALTER your table adding the new MAXVALUE partition

ALTER TABLE SAMPLE_ORDERS ADD PARTITION (
  PARTITION <partitionname> VALUES LESS THAN MAXVALUE
);

From the official documentation

MAXVALUE represents an integer value that is always greater than the largest possible integer value (in mathematical language, it serves as a least upper bound). Now, any rows whose ORDER_DATE column value is greater than or equal to 16 (the highest value defined) are stored in partition

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.

ALTER TABLE SAMPLE_ORDERS REORGANIZE PARTITION <partitionname> INTO (
    PARTITION SXXXQ1 VALUES LESS THAN TO_DATE(‘01-APR-2012’, ‘DD-MON-YYYY’),
    PARTITION <partitionname> VALUES LESS THAN MAXVALUE 
);

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

share|improve this answer
    
Thanks Cristian, I think I understand that bit I suppose I want to find out more specifics like if you alter a table adding a new range will it move data from one partition to another? So for example if I add the partition PARTITION SO00Q3a VALUES LESS THAN TO_DATE(‘15-OCT-2011’, ‘DD-MON-YYYY’) Will this move half the data in the SO00Q3 partition? –  user1434177 Aug 28 '12 at 23:29
    
No, you can't, if you add an intermediate partition you will recive this error "ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition" the onli way is to REORGANIZE PARTITION. –  Cristian Porta Aug 29 '12 at 6:06

In SQL Server you have to split the partition at the head of the table:

ALTER PARTITION SCHEME partition_scheme_name NEXT USED [ filegroup_name ];
ALTER PARTITION FUNCTION partition_function_name() SPLIT RANGE ( '20120401');

This will add a new partition for new incoming data. You should always an empty partition at the head of the table to make the split a instant metadata-only operation. If you populated the last partition with rows then splitting has to move data.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.