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

On Mon, Nov 1, 2010 at 2:11 PM, Clay Colburn <clay.colburn_at_gmail.com> wrote:

> 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>wrote:
>
>> Very cool, thanks Jared.
>>
>>
>> On Mon, Nov 1, 2010 at 11:44 AM, Jared Still <jkstill_at_gmail.com> wrote:
>>
>>> 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
>>>
>>>
>>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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