Create a key-preserved view across Oracle tables with descending indexes

oracleoracle-11g-r2updateview

I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and:

  • Do not have primary keys – only a unique index.
  • All fields are non-null, but one of the composite index fields is descending.

Is there any way I can create an updatable view across these two tables?

The real tables concerned contain a lot of data and are heavily used, so any changes / additions to the indexes would require significant regression testing, so I need to confine any solution to the view itself.

Problem seems to be something to do with Oracle creating separate composite function based indexes for the descending KEY2 fields. I've tried throwing a few hints into the view but am out of my depth here, and really not much idea what I'm doing.

Here's a simplified example. If I remove the "desc" from the two KEY2 index fields the final update works – if I leave it in it fails with: "ORA-01779: cannot modify a column which maps to a non key-preserved table".

create table TEST_KEY_PRES_A (
   KEY1 varchar2(8) not null,
   KEY2 varchar2(8) not null,
   VAL1 smallint not null,
   VAL2 varchar2(8) not null
)
;

create unique index TEST_KEY_PRES_A_IDX
on TEST_KEY_PRES_A (KEY1, KEY2 desc)
;

insert into TEST_KEY_PRES_A values ('K11', 'K21', 1, 'V2-1');
insert into TEST_KEY_PRES_A values ('K15', 'K25', 5, 'V2-5');

create table TEST_KEY_PRES_B (
   KEY1 varchar2(8) not null,
   KEY2 varchar2(8) not null,
   VAL3 varchar2(8) not null
)
;

create unique index TEST_KEY_PRES_B_IDX
on TEST_KEY_PRES_B (KEY1, KEY2 desc)
;

insert into TEST_KEY_PRES_B values ('K11', 'K21', 'V3-1');
insert into TEST_KEY_PRES_B values ('K15', 'K25', 'V3-5');

create view TEST_KEY_PRES_VW (KEY1, KEY2, VAL1, VAL2, VAL3) AS
select pa.KEY1, pa.KEY2, pa.VAL1, pa.VAL2, pb.VAL3
from   TEST_KEY_PRES_A pa
join   TEST_KEY_PRES_B pb on pa.KEY1 = pb.KEY1 and pa.KEY2 = pb.KEY2
where  pa.VAL1 > 3
;

update TEST_KEY_PRES_VW
set    VAL2 = 'V2-5-X'
where  KEY1 = 'K15'
;

SOLVED

Using INSTEAD OF trigger as suggested by Eduard:

create or replace trigger TEST_KEY_PRES_VW_UPD_TR
instead of update on TEST_KEY_PRES_VW
for each row
begin
    update TEST_KEY_PRES_A
    set    VAL1 = :new.VAL1, VAL2 = :new.VAL2
    where  KEY1 = :new.KEY1 and KEY2 = :new.KEY2;

    update TEST_KEY_PRES_B
    set    VAL3 = :new.VAL3
    where  KEY1 = :new.KEY1 and KEY2 = :new.KEY2;
end;

Best Answer

if you are not required to create view, but just to update using join, then we can use a workaround with a MERGE statement.

merge into TEST_KEY_PRES_A pa 
using TEST_KEY_PRES_B pb
  on (pa.KEY1 = pb.KEY1 and pa.KEY2 = pb.KEY2 and pa.VAL1 > 3 and pa.KEY1 = 'K15')
 when matched then update
   set VAL2 = 'V2-5-X';
Related Question