RE: Re: updating a table with the same value

In the spirit of quiz night, it might also depend on the timing of any presumptive commit or rollback succeeding the update.  

pffft.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ahmed.fikri_at_t-online.de Sent: Thursday, December 08, 2022 11:40 AM To: list oracle
Subject: AW: Re: updating a table with the same value  

Thank you sir for your answer and the link. the link is very interesting!

Gesendet mit der Telekom Mail App <http://www.t-online.de/service/redir/emailmobilapp_ios_smartphone_footerlink.htm>

-----Original-Nachricht-----

Von: Jonathan Lewis <jlewisoracle_at_gmail.com> Betreff: Re: updating a table with the same value Datum: 08.12.2022, 17:26 Uhr
An: list oracle <oracle-l_at_freelists.org>  

That's not a trivial question to answer; the mechanism would be affected by several factors, including the number of indexes that included the column, and the version of Oracle and, in more realistic cases, the distribution of changed and unchanged rows if you have a where clause which selected a subset of the total rows in the table.  

Read the following note to get an idea of how Oracle behaves. https://jonathanlewis.wordpress.com/2019/09/08/quiz-night-34/

Counter-intuitively, with your version of Oracle and in the absence of indexes the "no change" update might produce more undo and redo and operate more slowly than the "real change" updated.    

Regards

Jonathan Lewis      

On Thu, 8 Dec 2022 at 15:29, < ahmed.fikri_at_t-online.de> wrote:

Hello, everyone,

Could someone please tell if following queries produce the same amount of REDO/UNDO?

1. update table foo set status = 0;
2. update table foo set status = 1;

In both cases the status for all records is 0.

Are all blocks rewritten in both cases?

I’m using Oracle 19.3

Thanks
Ahmed

Gesendet mit der Telekom Mail App <http://www.t-online.de/service/redir/emailmobilapp_ios_smartphone_footerlink.htm>

--

http://www.freelists.org/webpage/oracle-l Received on Thu Dec 08 2022 - 21:30:25 CET