Re: Monster Update

Bit of a strange one this....

SQLPLUS AUTOTRACE reports that the SELECT does and Index FFS, and the UPDATE does an Index full scan...

But Tkprof reports that the SELECT does an index FFS and the UPDATE does a Full Table Scan....

I was trusting the output from SQLPLUS autotrace....

So it sounds like the update is full scanning the table.

Incidentally, tkprof reports 2 plans, the ROW SOURCE OPERATION, and EXECUTION PLAN.. Which one does it actually use during the query.

Thanks for you time

Matt

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 Wed Mar 10 2004 - 03:09:31 CST