MySQL Forums
Forum List  »  Partitioning

Re: make number of partitions variable
Posted by: Phil Hildebrand
Date: June 02, 2008 12:41AM

If you wanted to create the partitions automatically from code, you'd probably have to alter the procedure to use a prepared statement, and keep track of what the max partition is (maybe through information schema?). You could do it in php code as well, and then on some schedule (through cron or some other scheduler) execute the stored procedure to add the partitions.

(there may be a more efficient way, but this should work)

Here's an example to add the partitions:

DELIMITER //

CREATE PROCEDURE add_parts (IN mytable varchar(100))
BEGIN
DECLARE counter INT DEFAULT 10;
DECLARE partno INT DEFAULT 1;
DECLARE part_str VARCHAR(512);
DECLARE al_str TEXT;

SELECT max(partition_description)
FROM information_schema.partitions
WHERE table_name = mytable
AND partition_name != 'p_max'
INTO partno;

SET @al_str = CONCAT ('ALTER TABLE ', mytable, ' REORGANIZE PARTITION p_max INTO ( ');

WHILE counter > 0 DO
SET part_str = CONCAT( 'partition "p_', FROM_DAYS(partno), ' " VALUES LESS THAN (',partno + 1,'),' );
SET @al_str = CONCAT ( @al_str, part_str);
SET counter = counter - 1;
SET partno = partno + 1;
END WHILE;

SET @al_str = CONCAT ( @al_str, ' PARTITION p_max VALUES LESS THAN MAXVALUE );' );

PREPARE exec_str FROM @al_str;

EXECUTE exec_str;

DROP PREPARE exec_str;

END;
//

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: make number of partitions variable
2812
June 02, 2008 12:41AM


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.