Re: Monster Update

"Matt" <mccmx_at_hotmail.com> wrote in message news:cfee5bcf.0403100109.69a839d_at_posting.google.com...
> 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.

tkprof doesnt lie - how did you use it, with explain=y? That will tell you what it will do if you ran it now, tkprof on its own will tell you what it actually did. Received on Wed Mar 10 2004 - 16:04:24 CST