Re: Monster Update

From: FC <flavio_at_tin.it>
Date: Tue, 09 Mar 2004 21:23:39 GMT
Message-ID: <vBq3c.25976$O31.1145459@news4.tin.it>

"Matt" <mccmx_at_hotmail.com> wrote in message news:cfee5bcf.0403090405.5addc354_at_posting.google.com...
> 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')
>

Whatever your real intentions are, SET APP_DT = TO_DATE(SYSDATE, ...) is a nonsense that is causing Oracle to waste time in a twofold silly conversion because SYSDATE is already a date and is going to be converted implicitly in a VARCHAR2 and then back to a date. If you want to discard hours, minutes and seconds, use TRUNC(SYSDATE) and that's it.
It can only run faster after this change, no matter what the real perfomance problem is.

Bye,
Flavio Received on Tue Mar 09 2004 - 15:23:39 CST