RE: does update table with identical values actually write to disk?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "clay.colburn@xxxxxxxxx" <clay.colburn@xxxxxxxxx>, Jared Still <jkstill@xxxxxxxxx>
- Date: Mon, 1 Nov 2010 15:43:01 -0400
What if there's an index on the column being updated? :)
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxx<mailto:clay.colburn@xxxxxxxxx>> wrote:
Very cool, thanks Jared.
On Mon, Nov 1, 2010 at 11:44 AM, Jared Still
<jkstill@xxxxxxxxx<mailto:jkstill@xxxxxxxxx>> wrote:
On Mon, Nov 1, 2010 at 10:57 AM, Clay Colburn
<clay.colburn@xxxxxxxxx<mailto:clay.colburn@xxxxxxxxx>> 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
Other related posts:
