Impact of index changes on AI and replication

Posted by danielb on 13-Aug-2018 19:35

We are needing to make an index change to the primary index on one of our largest tables. The current index is something similar to:

index01 => fund, mem-ext, calculation-date, calculation-type

and we need to change it to

index01 => fund, mem-ext, calculation-type, calculation-date

There are a few different options, and we are unsure what the impact is on replication, AI and database activity for the following:

  1. Just change the index. This results in a rename to temp, add new index, and drop the temp index.
  2. Add a new index (index04), and update the primary index. Leave the existing index there as active and renamed and zz-.
  3. Add a new index (index04), update the primary index, and deactivate the existing index.

We're thinking of leading towards #2, maybe #3.

Cheers

All Replies

Posted by James Palmer on 14-Aug-2018 02:19

As far as I know none of these options should have an impact on AI or Replication. The index change will go into the AI and Replicate across.

Of course, to be 100% sure you'd want to test it ;)

Be careful with #3 as if you ever do an index rebuild of all indexes then the old index would be reactivated.

Posted by cjbrandt on 14-Aug-2018 08:09

If the table contains millions of records, the AI data generated from the index work may be significant.  Might want to see how much AI data is created from these tests.

This thread is closed