Calculate Performance Impact of Secondary Index

Database
Enthusiast

Calculate Performance Impact of Secondary Index

Is there a way to calculate the performance impact of adding a secondary index to a table. Either Unique or NON Unique.

2 REPLIES
Senior Apprentice

Re: Calculate Performance Impact of Secondary Index

If it's the first index on a table:

- a USI will double the number of IOs for Inserts/Update/Deletes and it needs communication between primary and secondary AMP, runtime might be doubled.

- for a NUSI it depends on the number of rows per value, maintaining a NUSI with a low number is much cheaper.

So it's really hard to tell without testing.

Enthusiast

Re: Calculate Performance Impact of Secondary Index

Thank you very much for the feedback .