Re: Performance Issue

Any index--unique or non-unique--that you create on a column that is heavily updated will slow down updates. This is because Oracle will have to maintain the index, that is, re-shuffle the internal index trees, which is a lot of work if you look closely at the process. The same basically holds true for insertions and deletions.

The best thing to do if you can is to not index columns that are heavily updated. Failing that, perhaps you can drop the indexes if the update in question is a batch run, and re-create the indexes when the batch job is complete. The Application Developer's Guide has some good guidelines on when to create (or not create) indexes.

Oh, and analyze your tables when appropriate if you are using cost-based optimization.

HTH Joe V.

Roman Gelfand wrote:
>
> Could somebody tell me if unique as opposed to non-unique index will slow
> down updates and/or access and why?
Received on Thu Sep 03 1998 - 00:00:00 CDT