delete in UJV

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)|

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