Re: Original design approach to Oracle REDO Logs

There are all sorts of odd details to worry about when you start to think about avoiding "no-change" updates.

Another is: if you enable supplemental logging using (e.g.) the primary key option, will the undo (hence redo) still record the primary key of the row that has been updated without being changed? And whatever the answer, should it or should it not?

Regards
Jonathan Lewis

On Fri, 18 Jun 2021 at 18:25, Frank Gordon <frankagordon_at_gmail.com> wrote:

> In this new scheme, where if the value is already the value that exists
> don't do the update, how should AFTER UPDATE FOR EACH ROW TRIGGERS work?
> Ignore the update if the value is being set to "itself"? Would that break
> some existing applications?
>
> Frank
>
>
>
> On Fri, Jun 18, 2021 at 4:02 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Mark,
>>
>> Don't forget the transformation would also have to cater for "column1 is
>> null", because that should be updated too (unless the update were to set it
>> to null).
>>
>> But the problem is a "notable change in behaviour" - the no-change rows
>> would not be locked.
>> I can't think of an example why this might be bad news in a well-designed
>> and coded sysatem, but imagine:
>>
>> user A: update rows set ownership = 'USERA' where ...';
>> user B: update rows set ownership = 'USERB' where ...;
>> user A: update where ownership='USERA';
>> user B: update where ownership='USERB'
>>
>> Assuming the ownership updates have some rows in common, and some of
>> those rows are already set to ownership = 'USERA'.
>>
>> Current implementation:
>> ===================
>> All userA rows are locked at step 1
>> User B waits at step 2 for User A to commit, then probably does a "write
>> consistent restart"
>>
>>
>> Transformed/Block image/Non-locking implementation
>> =========================================
>> User B at step 2 overwrites some rows which should be owned by userA
>> User A at step 3 waits for user B to commit, then probably does a "write
>> consistent restart".
>>
>> It shouldn't be too hard to produce refine this framework to produce an
>> example where the final values in the rows that both users were interested
>> in change because of the absence of locking.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>>
>> On Fri, 18 Jun 2021 at 15:15, Clay Jackson <dmarc-noreply_at_freelists.org>
>> wrote:
>>
>>> MWF wrote:
>>>
>>> “At the query level I wonder if permuting the query when it can be
>>> certainly iso-functional as
>>>
>>> update someTable
>>>
>>> set someColumn1 = 1.234
>>>
>>> becomes
>>>
>>> update someTable
>>>
>>> set someColumn1 = 1.234
>>>
>>> where someColumn1 != 1.234”
>>>
>>>
>>>
>>> I almost can’t believe I’m suggesting this; but is there a nugget of an
>>> “enhancement” request in there? I could see cases where if the optimizer
>>> was going to do an index lookup (i.e. there was a unique index on that
>>> column), OR, a full table scan (i.e. NO other path) a query rewrite might
>>> give some dramatic results (I think any paths other than “direct index” or
>>> full table scan would probably not be deterministic enough to “take a
>>> chance”).
>>>
>>>
>>>
>>> Clay Jackson
>>>
>>>
>>>
>>
>
> --
> +353-86-0695383
>

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