Skip to Content
Jan 14, 2020 at 05:56 PM

ASE Partition Indexes - how to alter an index to add new partitions and segments


I have the following index defined. It is referencing partitions defined on the table and storing the partition index data in a particular segment. The table is partitioned using range partitions that correspond to the month and year. I would like to alter this index and add new range partitions for year 2020 and reference a new 2020 segment for those new partitions. I'm trying to avoid having to drop the index and re-create it, since it's time consuming. We are working with hundreds of gigabytes of data.


CREATE nonclustered index idx2_audit_event ON audit_event(audit_event_timestamp, audit_sequence, audit_organization, audit_object, audit_column_name, LOWER(audit_identity)) local index event_M12019 on infraaudit2019seg, event_M22019 on infraaudit2019seg, event_M32019 on infraaudit2019seg, event_M42019 on infraaudit2019seg, event_M52019 on infraaudit2019seg, event_M62019 on infraaudit2019seg, event_M72019 on infraaudit2019seg, event_M82019 on infraaudit2019seg, event_M92019 on infraaudit2019seg, event_M102019 on infraaudit2019seg, event_M112019 on infraaudit2019seg, event_M122019 on infraaudit2019seg


Can I alter the index to add the new partition referencing a new segment? The partition event_M12020 is already defined on the table itself, and the infraaudit2020seg has already been created. Example (trying to do something like this):

ALTER index idx2_audit_event local index

add event_M12020 on infraaudit2020seg, event_M22020 on infraaudit2020seg


Any assistance greatly appreciated.