Re: Update takes an insanely long time

In article <833cpr$gv4$1_at_nnrp1.deja.com>,   decompton_at_worldnet.att.net wrote:
> In article <38502FDE.E3EDB5F1_at_abbnm.com>,
> Tapan Trivedi <tapan.trivedi_at_abbnm.com> wrote:
> > Hi gurus,
> > I have a question for you all. One of my developers is trying
> > to update a table with around 16500 records . She is trying a
> > very simple update statement update tablename set column1 = 'N'
> > where column1 = 'Y' She has around 12500 records which will be
> > affected. It has taken around 16 hours now for this simple
> > operation. Other tables on the same instance do not have any
> > problems for similar updates. There were about 35 constraints
> > on this table which I disable hoping to do better this time
> > without any success. Am I missing something obvious ? Should I
> > be concerned about anything else ??
> >
> > Any comments, suggestions , ideas will be appreciated.
> >
> > Tapan H Trivedi
> > Will be grateful if you could reply to the e-mail address also.
> >
> >
>
> In addition to the things already mentioned, verify that there are no
> update triggers on the table doing something unwanted or
> inefficiently. Unless you have a constraint on the column being
> updated, I doubt that RI is the problem.
>
> Also, check for locks when the update is running to make sure you
> aren't being blocked.
>
> thanks,
>
> dave
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

I agree that referential integrity should not be the source of the sluggishness, considering that you've disabled the constraints and performance doesn't improve.

The number of indexes you have may be a problem. In particular, I've experienced sluggish updates and inserts with BITMAP indexes due to the level of locking that the index update requires. If I remember correctly (and don't quote me on this) regular index updates only require record-level locking, whereas bitmap indexes require block-level locking. The solution to my problem many months ago was to drop the bitmap indexes, apply the update, then recreate them.

I'd also look for I/O contention, particularly where the TEMP tablespace, a high-volume transactional table or index, or any rollback segments may be involved.

Good luck,
Bill

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 13 1999 - 16:51:54 CST