Edit File: partition_reorganize_myisam.result
SET default_storage_engine=MyISAM; # # CREATE a table with SUBPARTITIONS # CREATE TABLE emp ( id INT NOT NULL, store_name VARCHAR(30), parts VARCHAR(30), store_id INT ) engine MyISAM PARTITION BY RANGE(store_id) SUBPARTITION BY HASH(store_id) ( PARTITION northeast VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' (SUBPARTITION ne0, SUBPARTITION ne1), PARTITION southwest VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' (SUBPARTITION sw2, SUBPARTITION sw3) ); INSERT INTO emp values(1,'Oracle','NUT',10); INSERT INTO emp values(2,'SAP','BOLT',40); INSERT INTO emp values(3,'IBM','NAIL',60); INSERT INTO emp values(4,'SUN','SCREW',90); SELECT * FROM emp; id store_name parts store_id 1 Oracle NUT 10 2 SAP BOLT 40 3 IBM NAIL 60 4 SUN SCREW 90 SHOW CREATE TABLE emp; Table Create Table emp CREATE TABLE `emp` ( `id` int(11) NOT NULL, `store_name` varchar(30) DEFAULT NULL, `parts` varchar(30) DEFAULT NULL, `store_id` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (store_id) SUBPARTITION BY HASH (store_id) (PARTITION northeast VALUES LESS THAN (50) (SUBPARTITION ne0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = MyISAM, SUBPARTITION ne1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_northeast' ENGINE = MyISAM), PARTITION southwest VALUES LESS THAN (100) (SUBPARTITION sw2 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = MyISAM, SUBPARTITION sw3 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_southwest' ENGINE = MyISAM)) */ ---- MYSQLD_DATADIR/test emp#P#northeast#SP#ne0.MYD emp#P#northeast#SP#ne0.MYI emp#P#northeast#SP#ne1.MYD emp#P#northeast#SP#ne1.MYI emp#P#southwest#SP#sw2.MYD emp#P#southwest#SP#sw2.MYI emp#P#southwest#SP#sw3.MYD emp#P#southwest#SP#sw3.MYI emp.frm emp.par ---- MYSQL_TMP_DIR/alt_dir_northeast emp#P#northeast#SP#ne0.MYD emp#P#northeast#SP#ne0.MYI emp#P#northeast#SP#ne1.MYD emp#P#northeast#SP#ne1.MYI ---- MYSQL_TMP_DIR/alt_dir_southwest emp#P#southwest#SP#sw2.MYD emp#P#southwest#SP#sw2.MYI emp#P#southwest#SP#sw3.MYD emp#P#southwest#SP#sw3.MYI # # REORGANIZE the PARTITIONS and SUBPARTITIONS # ALTER TABLE emp REORGANIZE PARTITION northeast INTO ( PARTITION east VALUES LESS THAN (25) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' (SUBPARTITION e0, SUBPARTITION e1), PARTITION north VALUES LESS THAN (50) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' (SUBPARTITION n0, SUBPARTITION n1) ); ALTER TABLE emp REORGANIZE PARTITION southwest INTO ( PARTITION west VALUES LESS THAN (75) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' (SUBPARTITION w0, SUBPARTITION w1), PARTITION south VALUES LESS THAN (100) DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' (SUBPARTITION s0, SUBPARTITION s1) ); SELECT * FROM emp; id store_name parts store_id 1 Oracle NUT 10 2 SAP BOLT 40 3 IBM NAIL 60 4 SUN SCREW 90 SHOW CREATE TABLE emp; Table Create Table emp CREATE TABLE `emp` ( `id` int(11) NOT NULL, `store_name` varchar(30) DEFAULT NULL, `parts` varchar(30) DEFAULT NULL, `store_id` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (store_id) SUBPARTITION BY HASH (store_id) (PARTITION east VALUES LESS THAN (25) (SUBPARTITION e0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = MyISAM, SUBPARTITION e1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_east' ENGINE = MyISAM), PARTITION north VALUES LESS THAN (50) (SUBPARTITION n0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = MyISAM, SUBPARTITION n1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_north' ENGINE = MyISAM), PARTITION west VALUES LESS THAN (75) (SUBPARTITION w0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = MyISAM, SUBPARTITION w1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_west' ENGINE = MyISAM), PARTITION south VALUES LESS THAN (100) (SUBPARTITION s0 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = MyISAM, SUBPARTITION s1 DATA DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' INDEX DIRECTORY = 'MYSQL_TMP_DIR/alt_dir_south' ENGINE = MyISAM)) */ ---- MYSQLD_DATADIR/test emp#P#east#SP#e0.MYD emp#P#east#SP#e0.MYI emp#P#east#SP#e1.MYD emp#P#east#SP#e1.MYI emp#P#north#SP#n0.MYD emp#P#north#SP#n0.MYI emp#P#north#SP#n1.MYD emp#P#north#SP#n1.MYI emp#P#south#SP#s0.MYD emp#P#south#SP#s0.MYI emp#P#south#SP#s1.MYD emp#P#south#SP#s1.MYI emp#P#west#SP#w0.MYD emp#P#west#SP#w0.MYI emp#P#west#SP#w1.MYD emp#P#west#SP#w1.MYI emp.frm emp.par ---- MYSQL_TMP_DIR/alt_dir_northeast ---- MYSQL_TMP_DIR/alt_dir_southwest ---- MYSQL_TMP_DIR/alt_dir_east emp#P#east#SP#e0.MYD emp#P#east#SP#e0.MYI emp#P#east#SP#e1.MYD emp#P#east#SP#e1.MYI ---- MYSQL_TMP_DIR/alt_dir_north emp#P#north#SP#n0.MYD emp#P#north#SP#n0.MYI emp#P#north#SP#n1.MYD emp#P#north#SP#n1.MYI ---- MYSQL_TMP_DIR/alt_dir_west emp#P#west#SP#w0.MYD emp#P#west#SP#w0.MYI emp#P#west#SP#w1.MYD emp#P#west#SP#w1.MYI ---- MYSQL_TMP_DIR/alt_dir_south emp#P#south#SP#s0.MYD emp#P#south#SP#s0.MYI emp#P#south#SP#s1.MYD emp#P#south#SP#s1.MYI DROP TABLE emp; # # Cleanup #