Hi Gingko, On Mon, Aug 5, 2024 at 4:38 PM Gingko via discuss <[email protected]> wrote: > So what I actually have to do is the following: > > 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 …
There is also a possibility to invoke ALTER TABLE … EXCHANGE PARTITION in order to convert each partition into a normal table and then to alter the table. That would seem to remove two full-copy steps while removing and adding partitioning. After converting each table (former partition), you'd create an empty partitioned table corresponding to the new PRIMARY KEY definition and EXCHANGE PARTITION from the individual tables again. I am not deeply familiar with the partitioning code, and I did not test the above suggestion. From the storage engine point of view, each partition or subpartition is just a funnily named table, and ALTER TABLE…EXCHANGE PARTITION is just a RENAME TABLE. > 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. > Or is this planned to some future version? > It is said that all columns in a table's partitioning function have to be > included in a primary key. > Is this really necessary? > Couldn't it just have to be included in any existing index key, not > necessarily the primary one? > Because of course, I cannot have several primary keys. > But if I could have more than one kind of index keys for sustaining > partitioning, at least I could add the needed one along with the primary key, > and then reduce the needed statements to execute at the number of two, thus > copying the table only twice. I can’t answer these questions, but I would assume that unless and until support for global indexes across all partitions is implemented, the primary key values must be disjoint between partitions. Best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
