Re: delete in UJV

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