RE: does update table with identical values actually write to disk?

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Mon, 1 Nov 2010 15:43:01 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F5E4C0482_at_AAPQMAILBX02V.proque.st>

What if there's an index on the column being updated? :)

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Clay Colburn Sent: Monday, November 01, 2010 3:12 PM
To: Jared Still
Cc: oracle-l
Subject: Re: does update table with identical values actually write to disk?

For those not inclined to run through the example, the answer was that it does not update the row, oracle intelligently skips the operation.

On Mon, Nov 1, 2010 at 12:10 PM, Clay Colburn <clay.colburn_at_gmail.com<mailto:clay.colburn_at_gmail.com>> wrote: Very cool, thanks Jared.

On Mon, Nov 1, 2010 at 11:44 AM, Jared Still <jkstill_at_gmail.com<mailto:jkstill_at_gmail.com>> wrote: On Mon, Nov 1, 2010 at 10:57 AM, Clay Colburn <clay.colburn_at_gmail.com<mailto:clay.colburn_at_gmail.com>> wrote: ... So for example if I have a row of data:

create table my_test(id number, val varchar(10)); insert into my_test(1, 'one');

and I run the statement

update my_test set val = 'one' where id = 1;

will it write the new value to disk or evaluate that it is the same and skip the write?

Check your undo and redo stats before and after the transaction and I think you will find your answer.

Here's a query to check the stats:

with chgstats as (

        select
                name.name<http://name.name> name,
                sum(stat.value) valuesum
        from v$mystat stat, v$statname name
        where
                stat.statistic# = name.statistic#
                and (name.name<http://name.name> like 'redo%' or name.name<http://name.name> like 'undo%')
        group by name.name<http://name.name>
        order by name.name<http://name.name>

)
select name, valuesum
from chgstats
where valuesum <> 0;

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 01 2010 - 14:43:01 CDT