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:[email protected]]
*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
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
