Hi

Marko,

Thinking again about that …

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 …
I understand that it may be not possible to do this by copying the table only once.

But does this really prevent allowing the combined statement?

At least by doing it the same way as the separated one (but using up to three temporary tables).

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 :
  1. Copy the table to temporary table #1 for partition removal without actually rebuilding the indexes.
  2. 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.
  3. 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.

Also, in step 2, you change only the index file, keeping the data unchanged, so for the data part, temporary table #1 could be simple renamed (instead of copied) to temporary table #2, also saving some time in the process.

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.

Regards,

Gingko

De : Marko Mäkelä [mailto:marko.makela@mariadb.com]
Envoyé : lundi 5 août 2024 à 4:09 PM
Pour : Gingko
Cc : MariaDB discuss
Objet : [MariaDB discuss] About partitioning change
Hi Gingko,

[…]

Is it really impossible to combine all these changes into a single statement, in order to copy the table only once, and save a lot of hours?
I think yes, it is impossible. The grammar of the SQL parser does not allow partition management operations such as REMOVE PARTITIONING to be combined with index management operations such as ADD INDEX.

[…]

Best regards,

Marko