Re: UPDATE SET of multiple columns

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Fri, 14 Apr 2006 16:21:19 GMT
Message-ID: <Ixq03M.6wL@igsrsparc2.er.usgs.gov>

> It works, but it updates every column in every row in the table...not
> fine it it has got 100M rows. And all columns not being null get
> updated to exactly the same value as before ---> lot of I/O generated
> accomplishing nothing.

True enough....lots of redo will be generated. But if the table in question has 100M rows, then 6 updates statements as I proposed:

    update DISK_STATS_TBL
    set DISKBSIZE_STAT = 0 where DISKBSIZE_STAT IS NULL;

    update DISK_STATS_TBL
    set DISKBUSY_STAT = 0 where DISKBUSY_STAT IS NULL;

    update DISK_STATS_TBL
    set DISKREAD_STAT = 0 where DISKREAD_STAT IS NULL;

etc.

will read all 100M rows six different times....i.e 600M rows. Due to the fact that the WHERE clause contains IS NULL, no index will be used, so a full table scan will result for each UPDATE statement. I'd be willing to be the I/O load for 100M rows will be better for the online redo logs in your one SQL statement than in my 6 UPDATE statements.

And you can always modify your single UPDATE statement to only update the affected rows as follows:

update DISK_STATS_TBL
set DISKBSIZE_STAT = nvl(DISKBSIZE_STAT,0),

	DISKBUSY_STAT  =nvl(DISKBUSY_STAT ,0),
	DISKREAD_STAT  =nvl(DISKREAD_STAT ,0),
	DISKWRITE_STAT =nvl(DISKWRITE_STAT ,0),
	DISKXFER_STAT  =nvl(DISKXFER_STAT ,0),
	DISKSERV_STAT  =nvl(DISKSERV_STAT ,0)
         DISKBSIZE_STAT =nvl(DISKBSIZE_STAT ,0)

WHERE DISKBSIZE_STAT IS NULL or DISKBUSY_STAT IS NULL or DISKREAD_STAT IS NULL or DISKWRITE_STAT IS NULL or DISKXFER_STAT IS NULL or DISKSERV_STAT IS NULL; The above will perform one full table scan (as opposed to 6 FTS's) and only change those rows that are affected. And in the case where one row would have more than one column change, the above command will only write one row's of changes to the online redo logs no matter how many columns are affected. My 6-statement solution would have written the same row changes to the online redo logs from 1 to 6 times depending on how many columns would have been affected in that row.

So taking your more elegant solution, and adding my *simple* WHERE clause, we've come up with a very efficient operation in a single SQL statement.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Fri Apr 14 2006 - 11:21:19 CDT