Re: Monster Update

It turns out that the SELECT is using the index but the UPDATE is doing a FTS...

So the waits are due to Multiblock I/O on the table blocks...

However this makes no sense because the update only ever updates one row of a 450,000 row table...

I have forced the update to use the index and it runs in less than 1 second.. But due to the Application being a packaged app I cannot change the SQL.

I am concerned about Oracle selecting a Full Scan approach to resolve this SQL....

Matt

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<LzP3c.16629$BA.11850_at_fed1read03>...
> 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
>
> Are any indexes being impacted by the UPDATE?
Received on Thu Mar 11 2004 - 05:24:06 CST