NO_DATA_NEEDED? I had to look that one up. For anyone else who does not
remember ever encountering this, the following might be of interest if you have
access to Oracle support.
Pipelined Function Terminates Unexpectedly without Reporting an Error when
NO_DATA_NEEDED or OTHERS Exception are not used. (Doc ID 1466789.1)
Under what Circumstances will a Pipleined PL/SQL Function Cause ORA-06548? (Doc
ID 1059985.1)
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Michael D O'Shea/Woodward Informatics Ltd
<woodwardinformatics@xxxxxxxxxxxxxxxx>
Sent: Friday, June 18, 2021 1:44 PM
To: frankagordon@xxxxxxxxx <frankagordon@xxxxxxxxx>
Cc: jlewisoracle@xxxxxxxxx <jlewisoracle@xxxxxxxxx>; ORACLE-L
<oracle-l@xxxxxxxxxxxxx>
Subject: AW: Original design approach to Oracle REDO Logs
Would that break some existing applications?
I think so.
This prospective behavioural change, or breaking change if you prefer, could be
circumvented by a developer, just as it was possible for the developer to
change the codebase to swallow the (silly) NO_DATA_NEEDED error and work around
the disappearance of (OK I know it was unsupported but I still used it)
WM_CONCAT function.
Mike
http://www.strychnine.co.uk<https://clicktime.symantec.com/3XPm8bU2vpgKvqDNUPNxAvd7Vc?u=http%3A%2F%2Fwww.strychnine.co.uk>
Am 18/06/2021 um 18:25 schrieb Frank Gordon
<frankagordon@xxxxxxxxx<mailto:frankagordon@xxxxxxxxx>>:
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@xxxxxxxxx<mailto:jlewisoracle@xxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:dmarc-noreply@xxxxxxxxxxxxx>> 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
