Re: UPDATE SET of multiple columns

On Fri, 14 Apr 2006 16:21:19 GMT, Brian Peasland <oracle_dba_at_nospam.peasland.net> wrote:

>> 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

It all depends on the data distribution and the size and row width of the table.

Your WHERE clause *may* reduce the work needed.

But let's say one of the 6 columns in question has 99% of its values NULL, (and the other columns having only 1% NULL).

Then the workload will be almost the same as without your WHERE.

And you will get one FTS, true. But that FTS will generate lots of redo, undo and possible locking issues.

The 6-step thing may trade these things for a lot of reading.But if 99% of all the columns are NULL anyway, then the 1-step thing is surely much better. 1% NULL's favors the 6-step. An enormous row width may favor the 1-step.

Which approach is most efficient depends on the actual data distribution, the size and row width of the table, how other applications are using the table in question and other concurrency issues.

For some not-clarified reason I am reluctant to update anything unnecessarily, that is why I would immediately choose the 6-step thing.

The point remains, though : A single statement may appear an elegant/efficient solution, but it need not be the most appropriate one.

• Kenneth Koenraadt