Re: Monster Update

From: Hari Vetsa <hari_at_vetsa.net>
Date: Tue, 9 Mar 2004 12:24:12 -0500
Message-ID: <c2kujt$dja$1@news.btv.ibm.com>

When you select It just scans the Index ( Using FFS ) However when you update three more operations needs to be done to original row ( Data block)

(*) Read the data block
(*) Acquire Lock
(*) Change the block

Which results in more IO and explains the IO wait.

"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')
>
> 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
Received on Tue Mar 09 2004 - 11:24:12 CST