Key preserved table improvements in 21c

Key preserved table is a cool feature available since Oracle 7.3
 
Let’s start with a simple demo using EMP and DEPT table
 
demo@ATP19C> create table emp as select * from scott.emp;
 
Table created.
 
demo@ATP19C> create table dept as select * from scott.dept;
 
Table created.
 
demo@ATP19C> alter table dept
  2  add constraint dept_pk
  3  primary key(deptno);
 
Table altered.
 
demo@ATP19C> alter table emp
  2  add constraint emp_fk
  3  foreign key(deptno)
  4  references dept;
 
Table altered.
 
Then a view that join these two tables
 
demo@ATP19C> create or replace view v
  2  as
  3  select e.empno,e.ename,e.sal,e.hiredate,d.dname,d.loc
  4  from emp e , dept d
  5  where e.deptno = d.deptno;
 
View created.
 
One of the nice features available since Oracle 7.3 is we can update this view, even though it is a join, we called it is a Key preserved table on EMP table (ie, for every row in that view maps to one row in the EMP table)
 
demo@ATP19C> select column_name,updatable,insertable,deletable
  2  from user_updatable_columns
  3  where table_name ='V' ;
 
COLUMN_NAM UPD INS DEL
---------- --- --- ---
EMPNO      YES YES YES
ENAME      YES YES YES
SAL        YES YES YES
HIREDATE   YES YES YES
DNAME      NO  NO  NO
LOC        NO  NO  NO
 
So we can update only those column in this view belong to the EMP table but not the DEPT table and that is what it is summarized in the above dictionary view.
 
Now let’s update the view on one of the columns from EMP table.
 
demo@ATP19C> update v
  2  set sal = 6000
  3  where empno = 7499;
 
1 row updated.
 
And the changes were available in the underlying base table too.
 
demo@ATP19C> select empno,ename,sal from v where empno = 7499;
 
     EMPNO ENAME             SAL
---------- ---------- ----------
      7499 ALLEN            6000
 
demo@ATP19C> select empno,ename,sal from emp where empno = 7499;
 
     EMPNO ENAME             SAL
---------- ---------- ----------
      7499 ALLEN            6000
 
Now let’s update those columns in this view belong to the DEPT table, and this is how it fails in Oracle 19c
 
demo@ATP19C> update v
  2  set dname = 'X'
  3  where empno = 7499 ;
set dname = 'X'
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
 
But now in 21c database.
 
demo@ATP21C> update v
  2  set dname = 'X'
  3  where empno = 7499 ;
 
1 row updated.
 
demo@ATP21C> select empno,ename,deptno
  2  from emp
  3  where empno = 7499 ;
 
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7499 ALLEN              30
 
demo@ATP21C> select dname from dept where deptno = 30;
 
DNAME
--------------
X
 
The above update works. This is not a “bug” in 21c instead it was a nice enhancement to Oracle 21c optimizer. If we are doing an update and if optimizer thinks it is safe then it was allowed to made the changes to the view.
 
So in the above case, the employee 7499 belongs to the dept no 30 and that dept no 30 maps to a single row in that DEPT table, so the update to the view was allowed. What happens if we update multiple rows in the view
 
demo@ATP21C> select empno,ename,deptno
  2  from emp
  3  where empno in ( 7499,7839 );
 
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7839 KING               10
      7499 ALLEN              30
 
demo@ATP21C> update v
  2  set dname = 'X'
  3  where empno in ( 7499,7839 );
 
2 rows updated.
 
demo@ATP21C> select deptno,dname from dept where deptno in (10,30);
 
    DEPTNO DNAME
---------- --------------
        10 X
        30 X
 
Since each employee in the above update maps to two different department and each department no maps to two different rows in the DEPT table it was allowed. Lets say what happens if we update multiple rows in the view that maps to same department number.
 
demo@ATP21C> select empno,ename,deptno
  2  from emp
  3  where empno in ( 7499,7698 );
 
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7698 BLAKE              30
      7499 ALLEN              30
 
demo@ATP21C> update v
  2  set dname = ename
  3  where empno in ( 7499,7698 );
update v
       *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
 
demo@ATP21C> update v
  2  set dname = 'X'
  3  where empno in ( 7499,7698 );
update v
       *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
 
So during the update if multiple rows of the child table maps to the same rows in the parent (in this case two different employees maps to same dept no) then the update is not be allowed to processed.