Hi Gingko, On Wed, Aug 7, 2024 at 3:28 PM Gingko <[email protected]> wrote: > You said that : > > ALTER TABLE `my_timeranges_table` REMOVE PARTITIONING; > ALTER TABLE `my_timeranges_table` DROP PRIMARY KEY, ADD PRIMARY KEY … > ALTER TABLE `my_timeranges_table` PARTITION BY RANGE … > > cannot be combined to: > > ALTER TABLE `my_timeranges_table` REMOVE PARTITIONING, DROP PRIMARY KEY, ADD > PRIMARY KEY …, PARTITION BY RANGE …
Yes, this is foremost a limitation of the SQL parser, as well as a limitation of how partition and operations around them have been implemented above the storage engine layer. > I understand that it may be not possible to do this by copying the table only > once. Have you considered a CREATE TABLE of a differently partitioned table followed by INSERT INTO…SELECT from the old-format partitioned table? That would involve copying the data only once. Possibly, to make this more efficient, you should initially create the new table without any secondary indexes, and execute ALTER TABLE…ADD INDEX…ADD INDEX at the end. > It seems to me that it could be nevertheless be somewhat optimized (because > the distinct statements also imply rebuilding the index three times whereas > this index will not have to be used in the middle), by doing it that way : > > Copy the table to temporary table #1 for partition removal without actually > rebuilding the indexes. > Copy temporary table #1 to temporary table #2 for executing the index > management operations, still without actually rebuilding the indexes. > Temporary table #1 can be deleted after the copy. > Copy temporary table #2 to temporary table #3 for building the new > partitioning scheme, effectively rebuilding the index only at the end of that > part. Temporary table #2 can be deleted after the copy and before the index > rebuild. > > This would certainly save a lot of time because the indexation part is very > important in the time needed for ALTER tables operations. I am mostly involved with the InnoDB storage engine. All InnoDB tables are index-organized, that is, the data is stored along with the primary key, and secondary indexes contain (secondary_key,primary_key) tuples that point to the primary key index. What you are suggesting could already be possible when using heap-organized tables with a storage engine that supports it. I am not sure if ENGINE=Aria supports DISABLE KEYS and ENABLE KEYS, which originated in ENGINE=MyISAM. > Last but not least, the whole operation being expected to be atomic, if > something fails in the middle, you just have to delete all temporary files, > and nothing will be changed at all. That is a challenge. To my understanding, many operations on partitioned tables are not really atomic internally. RENAME TABLE of a partitioned table might be, starting with MariaDB Server 10.6. Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
