Re: delete in UJV

I should have said that for the SQL Ref manual you'll need to search for the section "Notes on Updatable Views"
The SQL Ref manual describes what you see, the DBA manual says your delete isn't supposed to work at all.

Regards
Jonathan Lewis

On Thu, 11 Mar 2021 at 08:14, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> It's documented behaviour - but only if you read the right document first.
>
> I published a note about it a little while ago (
> https://jonathanlewis.wordpress.com/2019/08/20/join-view/ ) and the first
> comment I got included a link to a bit of documentation I hadn't previously
> read:
>
> SQL Language ref:
> https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-VIEW.html#GUID-61D2D2B4-DACC-4C7C-89EB-7E50D9594D30
> DBA Admin guied:
> https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-views-sequences-and-synonyms.html#GUID-C37E704C-AF40-42DA-85ED-372A62A74E58
>
>
>
> Regards
> Jonathan| Lewis
>
>
> On Thu, 11 Mar 2021 at 07:33, jaromir nemec <jaromir_at_db-nemec.com> wrote:
>
>> I often use the updatable join view for UPDATEs especially to fix large
>> tables after faulty running ETL jobs but I didn’t until recently
>> (https://stackoverflow.com/a/66391618/4808122)
>> have the experience with DELETE in UJV.
>>
>>
>> As this experience was interesting and rather unexpected, I want to share
>> it here.
>>
>> Here is a simple example of two tables BIG and DELTA. The DELTA contains a
>> unique list of ID's that should be deleted from the BIG table.
>>
>> create table big as
>> select
>> rownum id,
>> lpad('x',10,'Y') pad
>> from dual connect by level <= 1000000;
>>
>> create table delta as
>> select
>> rownum id
>> from dual connect by level <= 50;
>>
>>
>>
>> Note that if the DELTA table has no primary key or a unique index, the
>> delete fails with ORA-01752: cannot delete from view without exactly one
>> key-preserved table (which is fine).
>>
>> create unique index delta_idx on delta(id);
>>
>> delete from
>> (
>> select delta.id, big.id big_id
>> from delta
>> join big
>> on delta.id = big.id
>> )
>> ;
>>
>> 50 rows deleted.
>>
>> select count(*) from big;
>>
>> COUNT(*)
>> ----------
>> 999950
>>
>> As expected 50 rows from the BIG table were deleted.
>>
>> But now we repeate the delete (with a new table setup) and we define a
>> **unique index also on the BIG table**. What would happen?
>>
>> create unique index big_idx on big(id);
>>
>> delete from
>> (
>> select delta.id, big.id big_id
>> from delta
>> join big
>> on delta.id = big.id
>> )
>> ;
>>
>> select count(*) from big;
>>
>> COUNT(*)
>> ----------
>> 1000000
>>
>> select count(*) from delta;
>> COUNT(*)
>> ----------
>> 0
>>
>> Well, now the rows from the DELTA table were deleted! We can clearly see
>> it in the execution plan on line 1:
>>
>> Plan hash value: 508922691
>>
>>
>> ---------------------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
>> Time |
>>
>> ---------------------------------------------------------------------------------
>> | 0 | DELETE STATEMENT | | 50 | 400 | 51 (0)|
>> 00:00:01 |
>> | 1 | DELETE | DELTA | | | |
>> |
>> | 2 | NESTED LOOPS | | 50 | 400 | 51 (0)|
>> 00:00:01 |
>> | 3 | INDEX FULL SCAN | DELTA_IDX | 50 | 150 | 1 (0)|
>> 00:00:01 |
>> |* 4 | INDEX UNIQUE SCAN| BIG_IDX | 1 | 5 | 1 (0)|
>> 00:00:01 |
>>
>> ---------------------------------------------------------------------------------
>> ...
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> 4 - access("DELTA"."ID"="BIG"."ID")
>>
>> This test was done on 18.4 XE, but I thing this behaviour is version
>> independent.
>>
>> I'm not saying this is a bug, but intuitively I was expecting that in case
>> that both table are key preserved, Oracle would issue an error not knowing
>> where to delete.
>>
>> I did not found the logic behind why the DELTA table is subject of
>> deleting if both tables are candidates. I also failed to "hint" the
>> delete from the BIG table e.g. using the ORDERED hint.
>>
>> Any comments?
>>
>>
>> KR,
>>
>> Jaromir D.B Nemec
>>
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>

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