SQL Partioning on date, how does it work for future records?

Question

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?

Answer 1

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

Answer 2

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.