RE: Original design approach to Oracle REDO Logs
Date: Sat, 19 Jun 2021 08:04:27 -0400
Message-ID: <65ac01d76503$4135a160$c3a0e420$_at_rsiz.com>
yep. There are several problems with my suggestion. While it might have been valid for the application builder to build it that way, the system cannot change it under the covers for the reason you and others mentioned. It does not hold water.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, June 18, 2021 11:02 AM
To: ORACLE-L
Subject: Re: Original design approach to Oracle REDO Logs
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
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jun 19 2021 - 14:04:27 CEST