[oracle] 수정 가능 조인 뷰 (Updatable Join View)

조인을 통한  업데이트 작업시 보통 서브쿼리를 활용하게 되는데 

EMP 와 DEPT 라는 테이블을 예시로 들면 아래와 같은데.. 

UPDATE emp e
SET e.dname = (
  SELECT d.dname
  FROM dept d
  WHERE d.deptno = e.deptno
)
WHERE EXISTS (
  SELECT 1
  FROM dept d
  WHERE d.deptno = e.deptno
);

이 방식의 단점은 SET 절의 서브쿼리가 업데이트할 행마다 반복 실행된다는 점이다. 

단 건의 업데이트는 비효율이 크지 않지만 대량 업데이트 일수록 단점이 커진다. 

오라클에서는 이를 수정가능 조인뷰로 처리할수 있고 아래와 같다. 

UPDATE (
  SELECT e.dname AS e_dname,
         d.dname AS d_dname
  FROM emp e
  JOIN dept d ON e.deptno = d.deptno
  WHERE e.deptno IS NOT NULL
)
SET e_dname = d_dname; 

주의할 점은 1:M 관계에서 M 에 해당하는 컬럼만 수정이 가능한 제약이 있다. 

위의 경우에서는 dept 에 속한 컬럼을 수정하려고 하면 에러가 날 것이다. 

UPDATE (
  SELECT e.empno,,
 d.dname
  FROM emp e
  JOIN dept d ON e.deptno = d.deptno
  WHERE e.empno = 7788
)
SET dname = 'RESEARCH';

 => 에러발생 

ora-01779 cannot modify a column which maps to a non key-preserved table

 # 타 DBMS 에서는.. 

- PostgreSQL: 조인이 포함된 뷰는 기본적으로 수정 불가  (단, INSTEAD OF 트리거를 사용하면 우회 가능)
- MySQL: 일부 조건에서 조인 뷰 업데이트가 가능, 오라클만큼 유연하지는 않으며 제약이 많다.
- SQL Server: 조인 뷰 업데이트는 제한적, 대부분 INSTEAD OF 트리거를 사용