Monster Update

From: Matt <mccmx_at_hotmail.com>
Date: 9 Mar 2004 04:05:37 -0800
Message-ID: <cfee5bcf.0403090405.5addc354@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 - 06:05:37 CST