unique index

sourceuno · Post by **sourceuno** » Tue Nov 13, 2001 4:28 pm

Does the existence of unique indexes have any performance effect when performing updates to a table? I ran some updates on a table that has about 10 million records along with some unique indexes, but the script exited with an ABEND error. I assumed the indexes were corrupt so I removed them and re-ran the updates on the table without indexes, but now it seems like the updates are taking a long time. Do you have any tips for speeding up the performace of updates on a table?

Post by **John** » Tue Nov 13, 2001 4:52 pm

An index will speed up the updates if the where clause in the update can use the index. It does not make a difference if the index is unique or not. In general additional indexes will slow updates down slightly, as the indexes need to be maintained.

Post by **mark** » Tue Nov 13, 2001 4:53 pm

The key field for an update should be indexed. Otherwise it will have to linearly search for each record to update. A very slow process on a large table.

More indices obviously require a little more processing per record during inserts/deletes/updates.