Re: does update table with identical values actually write to disk?
- From: Clay Colburn <clay.colburn@xxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Mon, 1 Nov 2010 12:11:49 -0700
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>wrote:
> Very cool, thanks Jared.
>
>
> On Mon, Nov 1, 2010 at 11:44 AM, Jared Still <jkstill@xxxxxxxxx> wrote:
>
>> On Mon, Nov 1, 2010 at 10:57 AM, Clay Colburn <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 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
>>
>>
>
Other related posts:
