Re: Monster Update

Hi,

1. Check for triggers on this table.
2. Are you doing this query just after updating? It is possible that cache is getting utilized during query.

Hope this helps,
Jitendra Agrawal.
http://www.telemune.com/

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1078845040.615642_at_yasure>...
> Matt wrote:
>
> > I have an update statement which appears to be doing far more work
> > than required.
> >
> > The following statement hits the disk repeatedly (via multiblock read)
> > for an index fast full scan.
> >
> > UPDATE PS_TY_RECRUITMENT SET APP_DT = TO_DATE(SYSDATE,'YYYY-MM-DD')
> > WHERE APPLID = 'A0021198' AND APP_DT =
> > TO_DATE('2001-11-07','YYYY-MM-DD')
> >
> > The elapsed time is 30 seconds, of which over 17 seconds is spent
> > waiting on multiblock I/O.
> >
> > The strange part is that when I run a query which should effectively
> > perform the same lookup it returns instantly with no I/O wait
> > occuring.
> >
> > This is the SQL which returns immediately:
> >
> > SELECT APP_DT FROM PS_TY_RECRUITMENT
> > WHERE APPLID = 'A0021198' AND APP_DT =
> > TO_DATE('2001-11-07','YYYY-MM-DD')
> >
> > Both statements perform an index FFS so why does the update take so
> > much longer.....
> >
> > Any ideas would be greatly appreciated....
> >
> > Matt
>
> Run explain plan and a trace ... find out what is different.
Received on Tue Mar 09 2004 - 14:58:05 CST