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

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 1 Nov 2010 11:44:01 -0700
Message-ID: <AANLkTi=qKyNsevWyJ0rHdgHvV6p+W8ZRk=M6x=gViY5d_at_mail.gmail.com>

On Mon, Nov 1, 2010 at 10:57 AM, Clay Colburn <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 name,
                sum(stat.value) valuesum
        from v$mystat stat, v$statname name
        where
                stat.statistic# = name.statistic#
                and (name.name like 'redo%' or name.name like 'undo%')
        group by name.name
        order by 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 - 13:44:01 CDT