수정 가능한 조인 뷰: Key Preservation·결정성·안전한 대안

핵심 요약

수정 가능한 조인 뷰(Updatable Join View)는 여러 Table을 조인한 결과에 DML을 수행하지만, 실제 저장 변경은 한 번에 하나의 Base Table에만 적용됩니다. 판단 기준은 DML 종류에 따라 다릅니다.

• UPDATE: 한 Base Table의 Column만 변경하고, 같은 Base Row를 한 문장에서 두 번 이상 갱신하지 않는 결정성(Determinism)이 핵심입니다.
• INSERT: 입력 Column이 Key-Preserved Table 하나에 속해야 합니다.
• DELETE: 삭제 대상이 되는 Key-Preserved Table을 식별해야 합니다.

Key-Preserved Table은 여전히 중요한 기본 개념이지만, Oracle Database 21c 이후에는 Join View UPDATE의 허용 범위가 확장되었습니다. Non-Key-Preserved Table의 Column이라도 UPDATE가 한 Table만 변경하고 각 Base Row를 정확히 한 번만 갱신하면 허용될 수 있습니다. 반대로 같은 Base Row가 Join 결과에 여러 번 나타나면 ORA-30926이 발생할 수 있습니다.

학습 목표

1. Join View DML이 실제로 변경하는 Base Table을 식별한다.
2. Key-Preserved Table과 결정성의 차이를 설명한다.
3. Oracle 21c 이후 Join View UPDATE 규칙 변화를 이해한다.
4. ORA-01776, ORA-30926, ORA-01427의 발생 구조를 구분한다.
5. INSERT·UPDATE·DELETE의 Join View 규칙을 구분한다.
6. USER_UPDATABLE_COLUMNS의 용도와 한계를 이해한다.
7. WITH CHECK OPTION과 비본질적 수정 가능 View의 제한을 판단한다.
8. UPDATE ... FROM, MERGE, 상관 UPDATE, INSTEAD OF Trigger를 안전한 대안으로 선택한다.

1. 조인 뷰 DML의 공통 원칙

Join View는 Top-Level FROM 절에 둘 이상의 Table 또는 View가 있는 View입니다. DML의 공통 원칙은 다음과 같습니다.

1. 한 DML 문은 Join View를 통해 하나의 Base Table만 변경할 수 있습니다.
2. 변경 대상 Column이 어느 Base Table에 속하는지 먼저 확인합니다.
3. Join 결과에서 같은 Base Row가 몇 번 나타나는지 계산합니다.
4. 현재 데이터가 우연히 유일한지보다 PK·Unique Constraint가 구조적으로 유일성을 보장하는지 확인합니다.

서로 다른 Base Table의 Column을 한 문장에서 동시에 변경하면 ORA-01776: cannot modify more than one base table through a join view가 발생할 수 있습니다.

2. Key-Preserved Table

Table의 Primary Key 또는 Unique Key가 Join 결과에서도 유일하게 유지되면 그 Table을 Key-Preserved Table이라 합니다. 쉽게 말해 해당 Table의 한 Base Row가 Join 결과에 최대 한 번만 나타나는 구조입니다.

다음 Table을 가정합니다.

CREATE TABLE employee (
  emp_id    NUMBER PRIMARY KEY,
  grade     VARCHAR2(10),
  salary    NUMBER
);

CREATE TABLE salary_grade (
  grade       VARCHAR2(10) PRIMARY KEY,
  raise_rate  NUMBER
);

SALARY_GRADE.GRADE가 Unique이므로 한 Employee Row는 최대 한 Grade Row와 연결됩니다.

EMPLOYEE 한 행
× SALARY_GRADE 최대 한 행
→ EMPLOYEE Key가 Join 결과에서 유일
→ EMPLOYEE는 Key-Preserved

반대로 같은 Grade를 가진 Employee가 여러 명이면 한 SALARY_GRADE Row가 Join 결과에 여러 번 나타날 수 있으므로 SALARY_GRADE는 일반적으로 Key-Preserved가 아닙니다.

Key Preservation은 현재 데이터의 우연한 중복 여부가 아니라 Schema의 PK·Unique Constraint와 Join 조건으로 판단합니다.

3. Oracle 21c 이후 UPDATE 결정성 규칙

전통적으로 Join View의 수정 가능성은 Key-Preserved Table 중심으로 설명했습니다. Oracle 21c 이후에는 UPDATE에 한해 다음 조건을 만족하면 Non-Key-Preserved Table의 Column도 수정될 수 있습니다.

• 변경 Column이 한 Base Table에만 속함
• 같은 Base Row를 한 문장에서 정확히 한 번만 갱신함
• UPDATE가 결정적임

3.1 Key-Preserved Table 갱신

UPDATE (
  SELECT e.salary,
         g.raise_rate
  FROM   employee e
  JOIN   salary_grade g
    ON   g.grade = e.grade
)
SET salary = salary * (1 + raise_rate);

EMPLOYEE Row가 Join 결과에 한 번만 나타나므로 EMPLOYEE.SALARY 갱신은 결정적입니다.

3.2 Non-Key-Preserved Table의 결정적 갱신

SALARY_GRADE는 여러 Employee와 연결될 수 있어 일반적으로 Non-Key-Preserved입니다. 그러나 Employee의 PK로 한 행만 선택하여 결과적으로 Grade Row 하나를 한 번만 변경한다면 UPDATE가 허용될 수 있습니다.

UPDATE (
  SELECT e.emp_id,
         g.raise_rate
  FROM   employee e
  JOIN   salary_grade g
    ON   g.grade = e.grade
)
SET raise_rate = 0.15
WHERE emp_id = 100;

핵심은 EMP_ID=100이 한 Employee Row만 선택하고, 그 Row가 연결된 Grade Row를 한 번만 갱신한다는 점입니다.

3.3 비결정적 갱신과 ORA-30926

같은 Grade의 Employee가 여러 명인데 다음처럼 여러 Employee Row를 통해 동일한 Grade Row를 반복 갱신하려 하면 비결정적입니다.

UPDATE (
  SELECT e.emp_id,
         e.grade,
         g.raise_rate
  FROM   employee e
  JOIN   salary_grade g
    ON   g.grade = e.grade
)
SET raise_rate = 0.15
WHERE grade = 'A';

Grade A Employee가 두 명이면 동일한 SALARY_GRADE('A') Row를 두 번 갱신하려 하므로 ORA-30926이 발생할 수 있습니다.

결정성은 “최종 값이 우연히 같아 보이는가”가 아니라 “한 Base Row를 한 문장에서 한 번만 변경하는가”로 판단합니다.

4. DML 종류별 규칙

4.1 UPDATE

• 한 Base Table의 Column만 변경합니다.
• UPDATE는 각 Base Row를 한 번만 변경해야 합니다.
• Key-Preserved Table이면 판단이 단순합니다.
• Non-Key-Preserved Table도 Oracle 21c 이후 결정적 UPDATE라면 허용될 수 있습니다.
• 같은 Base Row가 여러 Join Row를 통해 갱신되면 ORA-30926 위험이 있습니다.

4.2 INSERT

Join View INSERT는 UPDATE보다 엄격합니다.

• Insert Column은 하나의 Key-Preserved Table에 속해야 합니다.
• Non-Key-Preserved Table Column을 포함하거나 둘 이상의 Base Table Column을 함께 Insert할 수 없습니다.
• 둘 이상의 Base Table을 변경하려 하면 ORA-01776이 발생할 수 있습니다.

4.3 DELETE

Join View DELETE는 삭제 대상 Base Table을 Key Preservation으로 판단합니다.

• 일반적으로 삭제 대상이 되는 Key-Preserved Table을 식별해야 합니다.
• 둘 이상의 Key-Preserved Table이 존재하는 복잡한 View에서 FROM 절 순서에 의존하는 동작은 유지보수 위험이 크므로 명시적인 Base Table DELETE를 우선합니다.
• Join View에 WITH CHECK OPTION이 있거나 동일 Table이 반복 참조되면 제한이 강화될 수 있습니다.

5. 본질적으로 수정 불가능한 View 구조

다음 구조는 Base Row와 View Row의 직접 대응을 바꾸므로 일반적으로 본질적으로 수정 가능한 View가 아닙니다.

• Set Operator
• DISTINCT
• Aggregate 또는 Analytic Function
• GROUP BY, ORDER BY, MODEL, CONNECT BY, START WITH
• Select List의 Subquery 또는 Collection Expression
• Recursive WITH
• WITH READ ONLY

예를 들어 부서 평균 급여 한 행은 여러 Employee Row에서 계산되므로 어느 Base Row를 변경할지 직접 대응이 없습니다.

SELECT department_id,
       AVG(salary) AS avg_salary
FROM   employee
GROUP BY department_id;

이런 View에 DML이 필요하면 Base Table DML로 다시 작성하거나 INSTEAD OF Trigger로 업무 규칙을 명시합니다.

6. WITH CHECK OPTION

WITH CHECK OPTION은 View를 통해 변경한 Row가 계속 View 조건을 만족하도록 제한합니다. Join View에서는 수정 가능성이 더 제한되며, Join Column이나 같은 Table이 반복 참조되는 Column 등이 수정 불가능해질 수 있습니다.

따라서 다음을 함께 확인합니다.

1. View가 WITH CHECK OPTION으로 생성됐는가?
2. 변경하려는 Column이 Join 조건에 참여하는가?
3. 변경 후 Row가 View 조건에서 사라지는가?
4. 실제 Version에서 DML이 허용되는가?

7. USER_UPDATABLE_COLUMNS의 용도와 한계

SELECT table_name,
       column_name,
       updatable,
       insertable,
       deletable
FROM   user_updatable_columns
WHERE  table_name = 'EMP_GRADE_V'
ORDER BY column_name;

이 View는 Join View Column의 수정 가능성 판단에 유용하지만 다음 한계를 알아야 합니다.

1. Oracle 21c 이후에는 UPDATABLE='NO'인 Non-Key-Preserved Table Column도 해당 UPDATE가 한 Table만 변경하고 결정적이면 갱신될 수 있습니다.
2. Base Table에 PK·Unique Constraint를 추가하거나 제거한 직후 값이 즉시 갱신되지 않을 수 있습니다.
3. Constraint 변경 후에는 ALTER VIEW ... COMPILE로 View를 재컴파일하고 다시 조회합니다.
4. Dictionary 결과만 믿지 말고 Test Transaction에서 DML을 실행한 뒤 ROLLBACK하여 실제 동작을 검증합니다.

8. 안전한 대안 패턴

8.1 UPDATE ... FROM

Oracle 26ai에서는 Source를 FROM 절에 명시하는 Direct-Join UPDATE를 사용할 수 있습니다.

UPDATE employee e
SET    salary = salary * (1 + g.raise_rate)
FROM   salary_grade g
WHERE  g.grade = e.grade;

같은 Target Row가 Join 결과에 여러 번 나타나면 ORA-30926 위험이 있으므로 Source Join Key 유일성을 확인합니다.

8.2 MERGE

MERGE INTO employee e
USING salary_grade g
ON (g.grade = e.grade)
WHEN MATCHED THEN
  UPDATE SET e.salary = e.salary * (1 + g.raise_rate);

MERGE는 Source·Target 역할을 명확히 표현하지만 Source 중복을 자동 해결하지 않습니다. 같은 Target Row가 여러 Source Row와 Match하면 비결정적이므로 ORA-30926이 발생할 수 있습니다.

8.3 상관 UPDATE

UPDATE employee e
SET salary = salary * (
      1 + (
        SELECT g.raise_rate
        FROM   salary_grade g
        WHERE  g.grade = e.grade
      )
    )
WHERE EXISTS (
      SELECT 1
      FROM   salary_grade g
      WHERE  g.grade = e.grade
    );

Scalar Subquery가 두 행 이상을 반환하면 ORA-01427이 발생하여 Source 유일성 문제를 드러냅니다. 다만 반복 Probe 비용은 Starts, A-Rows, Buffers로 확인해야 합니다.

8.4 사전 집계·순위 후 갱신

Source 중복에서 업무적으로 최신 한 행을 선택해야 한다면 임의 DISTINCT 대신 명시적인 규칙을 사용합니다.

MERGE INTO employee e
USING (
  SELECT grade, raise_rate
  FROM (
    SELECT h.*,
           ROW_NUMBER() OVER (
             PARTITION BY grade
             ORDER BY effective_date DESC,
                      grade_rule_id DESC
           ) AS rn
    FROM salary_grade_history h
  )
  WHERE rn = 1
) s
ON (s.grade = e.grade)
WHEN MATCHED THEN
  UPDATE SET e.salary = e.salary * (1 + s.raise_rate);

Tie-Breaker까지 포함해 Target Key당 Source 한 행을 결정해야 합니다.

8.5 INSTEAD OF Trigger

본질적으로 수정 불가능한 View에 DML Interface가 필요하면 INSTEAD OF Trigger로 Base Table 변경 규칙을 구현할 수 있습니다.

• 어떤 Base Table을 어떤 순서로 변경할지 명시할 수 있습니다.
• 오류 처리와 업무 무결성을 직접 구현해야 합니다.
• Row별 Trigger 실행 비용과 숨은 Side Effect를 측정해야 합니다.

9. 결정성 검증 SQL

Source가 Target Join Key당 한 행인지 확인합니다.

SELECT grade, COUNT(*)
FROM   salary_grade
GROUP BY grade
HAVING COUNT(*) > 1;

Join 결과에서 Target Row가 증식하는지도 확인합니다.

SELECT e.emp_id, COUNT(*) AS join_rows
FROM   employee e
JOIN   salary_grade g
  ON   g.grade = e.grade
GROUP BY e.emp_id
HAVING COUNT(*) > 1;

결과가 0건이라는 사실보다 PK·Unique Constraint가 미래 데이터에도 유일성을 보장하는지가 중요합니다.

10. 혼동하기 쉬운 판단

11. 실행계획과 실측에서 확인할 항목

1. 실제 변경 Base Table과 Column
2. Join 전후 A-Rows와 Target Row 증식 여부
3. Source Row Source의 Starts, A-Rows, Buffers
4. PK·Unique Constraint와 Join Predicate
5. USER_UPDATABLE_COLUMNS와 View Compile 상태
6. 실제 변경 건수와 예상 업무 건수
7. ORA-01776, ORA-30926, ORA-01427 발생 조건
8. MERGE·UPDATE FROM·상관 UPDATE 간 Plan과 Redo 비교
9. 동시 실행 시 Row Lock·Unique Constraint 대기

12. 적용 판단 순서

1. DML 종류를 UPDATE·INSERT·DELETE로 구분합니다.
2. 실제 변경할 Base Table을 하나로 확정합니다.
3. Target Base Row가 Join 결과에 몇 번 나타나는지 계산합니다.
4. PK·Unique Constraint로 유일성을 검증합니다.
5. UPDATE라면 21c 이후 결정성 예외를 포함해 판단합니다.
6. INSERT·DELETE라면 Key Preservation 규칙을 우선 확인합니다.
7. WITH CHECK OPTION과 비본질적 수정 가능 구조를 확인합니다.
8. Dictionary 조회 후 View를 재컴파일하고 Test Transaction에서 검증합니다.
9. 복잡한 경우 UPDATE FROM·MERGE·상관 UPDATE·INSTEAD OF Trigger 중 의도가 가장 명확한 방식을 선택합니다.
10. 변경 건수·NULL·중복·실행 통계를 전후 비교합니다.