태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Updatable Join Views vs. Merge vs. Instead of Trigger

오라클 2008.12.22 17:02
여러 개의 Table이 Join된 View를 Update할 수 있다는 것을 잘 알고 있을 것이다. Oracle은 이것을 Updatable Join View라고 부른다.

View(단, Subquery까지 포함)를 Update할 수 있는 방법은 내가 알기로는 세 가지 정도가 있다.

- Updatable Join View
- Merge Statement(정확하게 말하면 View는 아니지만 그런 효과가 있다는 의미)
- Instead of Trigger

역시 하나의 문제에 대해 여러 가지 솔루션이 있을 수 있다는 것을 아는 것이 중요하다는 것의 예가 되겠다.

간단한 예를 통해 각 방법들을 살펴 보면...

우선 다음과 같이 Parent/Child 관계를 가지는 Table을 만든다.

drop table t1 purge;
drop table t2 purge;

create table t1(c1 int, c2 int);
create table t2(c1 int, c2 int);

insert into t1
select level, level
from dual
connect by level <= 10
;

insert into t2
select mod(level, 10)+1, level
from dual
connect by level <= 100
;

commit;


Data만으로 보면 Parent(T1)/Child(T2) 관계이지만 Oracle이 이 관계를 파악할 수 있는 방법은 없다.

다음과 같이 두 개의 View를 만든다. View의 "Column"에 유의하자.

create or replace view v1
as
select
  t1.c1, t2.c2
from
  t1, t2
where
  t1.c1 = t2.c1
  and t1.c1 between 1 and 2
;


이 View는 Updatable한가? user_updatable_columns 뷰를 보면 알 수 있다.

select
  column_name, updatable, insertable, deletable
from
  user_updatable_columns
where
  table_name = 'V1'
;

COLUMN_NAME          UPDATABLE INSERTABL DELETABLE
-------------------- --------- --------- ---------
C1                   NO        NO        NO       
C2                   NO        NO        NO   
    

전혀 Updatable하지 않다. Oracle은 Parent/Child 관계를 알 수 없기 때문이다.
View의 특정 Column이 Updatable하려면 Key-Preserved해야 하는데(Oracle의 용어이다), Key-Preserved한지를 Oracle이 판단할 수 없기 때문이다.

같은 이유로 다음과 같은 View도 전혀 Updatable하지 않다.

create or replace view v2
as
select
  t2.c1, t2.c2
from
  t1, t2
where
  t1.c1 = t2.c1
  and t1.c1 between 1 and 2
;

select
  column_name, updatable, insertable, deletable
from
  user_updatable_columns
where
  table_name = 'V2'
;
COLUMN_NAME          UPDATABLE INSERTABL DELETABLE
-------------------- --------- --------- ---------
C1                   NO        NO        NO       
C2                   NO        NO        NO    
   

Updatable하지 않기 때문에 다음과 같이 Subquery(View)에 대한 Update는 실패한다.

update (
  select
    t1.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  ) x
set x.c2 = x.c2 + 1000
;

ERROR at line 10:
ORA-01779: cannot modify a column which maps to a non key-preserved table


update (
  select
    t2.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  ) x
set x.c2 = x.c2 + 1000
;

ERROR at line 10:
ORA-01779: cannot modify a column which maps to a non key-preserved table


이 문제를 해결하는 한가지 안좋은 방법은 /*+ BYPASS_UJVC */ Hint를 사용하는 것이다. 말 그대로 Updatable Join View Constraint(제약 조건)을 Bypass한다는 것이다.

update /*+ bypass_ujvc */ (
  select
    t1.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  ) x
set x.c2 = x.c2 + 1000
;

20 rows updated.



궁극적인 해결책은 Oracle에게 Parent/Child 관계를 알려 주는 것이다.

더 정확하게 이야기하면 t1:t2 = 1:N이며 t1.c1이 Unique하기 때문에 t2와 관련된 Column들은 Updatable하다는 것을 알려 주어야 한다.

다음과 같이 t1.c1에 primary key를 추가함으로써 이를 달성할 수 있다.

alter table t1 add constraint t1_pk primary key (c1);


다음과 같이 일부 혹은 전체 Column들이 Updateable하다는 것을 확인할 수 있다.

alter view v1 compile;
alter view v2 compile;

select
  column_name, updatable, insertable, deletable
from
  user_updatable_columns
where
  table_name = 'V1'
;
COLUMN_NAME          UPDATABLE INSERTABL DELETABLE
-------------------- --------- --------- ---------
C1                   NO        NO        NO       
C2                   YES       YES       YES      


select
  column_name, updatable, insertable, deletable
from
  user_updatable_columns
where
  table_name = 'V2'
;
COLUMN_NAME          UPDATABLE INSERTABL DELETABLE
-------------------- --------- --------- ---------
C1                   YES       YES       YES     
C2                   YES       YES       YES    
 

따라서 다음과 같이 Join View를 Update할 수 있다.
더 정확하게 말하면 user_updatable_columns 뷰에서 'YES'로 나온 Column에 대해서는 update/insert/delete가 가능하다.

update (
  select
    t1.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  ) x
set x.c2 = x.c2 + 1000
;
20 rows updated.


다음과 같은 Insert 구문은 어떨까? 성공할까?

insert into (
    select
    t1.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  )
values(1, 11)
;


Column t1.c1은 Updatable하지 않기 때문에 다음과 같이 에러가 난다.

ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


반면 다음과 같은 Insert는 성공한다.
Key-Preserved인 Table에 대해서만 Insert가 이루어지기 때문이다.

insert into (
    select
    t2.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  )
values(1, 11)
;

1 row created.



다음과 같이 Delete도 가능하다.

delete from (
  select
    t1.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  ) x
;

20 rows deleted.


View에 대한 Update는 어떤 실행 계획을 보일까? 아래에 그 결과가 있다.

explain plan for
update (
  select
    t1.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  ) x
set x.c2 = x.c2 + 1000
;


매우 논리적인 실행 계획을 보인다.

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |       |     4 |   156 |     4   (0)| 00:00:01 |
|   1 |  UPDATE             | T2    |       |       |            |          |
|   2 |   NESTED LOOPS      |       |     4 |   156 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T2    |    20 |   520 |     4   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN| T1_PK |     1 |    13 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):  
---------------------------------------------------  
                                                     
   3 - filter("T2"."C1">=1 AND "T2"."C1"<=2)         
   4 - access("T1"."C1"="T2"."C1")                   
       filter("T1"."C1">=1 AND "T1"."C1"<=2)         



논의를 더 확장해보자.
이런 Query에서 Parallel Execution은 가능한가?
안될 이유가 없다.

explain plan for
update (
  select /*+ parallel(t2) parallel(t1) no_index(t2) no_index(t1) */
    t1.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  ) x
set x.c2 = x.c2 + 1000
;


실행 계획은 다음과 같다.

-------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes |
-------------------------------------------------------------
|   0 | UPDATE STATEMENT         |          |     4 |   156 |
|   1 |  UPDATE                  | T2       |       |       |
|   2 |   PX COORDINATOR         |          |       |       |
|   3 |    PX SEND QC (RANDOM)   | :TQ10001 |     4 |   156 |
|*  4 |     HASH JOIN            |          |     4 |   156 |
|   5 |      PX RECEIVE          |          |     2 |    26 |
|   6 |       PX SEND BROADCAST  | :TQ10000 |     2 |    26 |
|   7 |        PX BLOCK ITERATOR |          |     2 |    26 |
|*  8 |         TABLE ACCESS FULL| T1       |     2 |    26 |
|   9 |      PX BLOCK ITERATOR   |          |    20 |   520 |
|* 10 |       TABLE ACCESS FULL  | T2       |    20 |   520 |
-------------------------------------------------------------


View 부분(Select ...)은 명백하게 병렬 실행이 된다.
하지만 Update 부분은 병렬 실행이 되지 않는 것을 알 수 있다.

Parallel DML은 다음과 같이 명시적으로 활성화를 해야만 실행이 된다.

alter session enable parallel dml;

explain plan for
update (
  select /*+ parallel(t2) parallel(t1) no_index(t2) no_index(t1) */
    t1.c1, t2.c2
  from
    t1, t2
  where
    t1.c1 = t2.c1
    and t1.c1 between 1 and 2
  ) x
set x.c2 = x.c2 + 1000
;

---------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes |
---------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |          |     4 |   156 |
|   1 |  PX COORDINATOR                  |          |       |       |
|   2 |   PX SEND QC (RANDOM)            | :TQ10002 |     4 |   156 |
|   3 |    UPDATE                        | T2       |       |       |
|   4 |     PX RECEIVE                   |          |     4 |   156 |
|   5 |      PX SEND HASH (BLOCK ADDRESS)| :TQ10001 |     4 |   156 |
|*  6 |       HASH JOIN                  |          |     4 |   156 |
|   7 |        PX RECEIVE                |          |     2 |    26 |
|   8 |         PX SEND BROADCAST        | :TQ10000 |     2 |    26 |
|   9 |          PX BLOCK ITERATOR       |          |     2 |    26 |
|* 10 |           TABLE ACCESS FULL      | T1       |     2 |    26 |
|  11 |        PX BLOCK ITERATOR         |          |    20 |   520 |
|* 12 |         TABLE ACCESS FULL        | T2       |    20 |   520 |
---------------------------------------------------------------------



Merge 구문이 등장함으로써 Updatable Join View의 기능을 어느 정도 대신할 수 있게 되었다.
아래와 같이 Insert가 없는 Merge 구문을 사용하면 똑같은 효과를 얻을 수 있다.
(더 직관적이기도 하다)

merge into t2
using (select c1, c2 from t1
          where c1 between 1 and 2) x
on (x.c1 = t2.c1)
when matched
  then update set t2.c2 = t2.c2 + 1000
;

20 rows merged.


Updatable Join View나 Merge를 이용해서 하나의 Query로 View에 대해 DML이 불가능하다면?
개별 Table에 대한 여러 번의 DML로 나누어서 수행할 수 밖에 없는가?
만일 권한 관리 때문에 View에 대한 권한밖에 줄 수 없다면?

이런 상황을 해결할 수 있는 마지막 수단이 Instead of Trigger이다.

가령 다음과 같은 View v1에 대한 Insert 문은 (당연하게도) 모두 실패한다.
(Column t1.c1은 Updatable하지 않기 때문이다)

insert into v1
values(1, 10)
;
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

insert into v1
select values(100, 100);
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

/*+ bypass_ujvc */ Hint를 사용하면 해결될까?

insert /*+ bypass_ujvc */ into v1 values(1, 10)
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view

View v1은 Table t1, t2의 Column을 모두 참조하고 있기 때문에 두 개의 Table에 대해 Insert하는 것이 불가능하다.

이 문제를 해결할 수 있는 한가지 방법은 View에 대한 Trigger(Instead of Trigger)를 만드는 것이다.

create or replace trigger trg1
instead of insert on v1
for each row
declare

begin
  insert into t1 values(:new.c1, 100);
  insert into t2 values(:new.c1, 100);
 
end;
/

다음과 같이 Insert가 모두 성공한다.

insert into v1
values(1, 10)
;
ERROR at line 1:
ORA-00001: unique constraint (UKJA.T1_PK) violated
ORA-06512: at "UKJA.TRG1", line 4
ORA-04088: error during execution of trigger 'UKJA.TRG1'

insert into v1
values(100, 100);

1 row created.


역시 가장 중요한 것은 하나의 문제에 대해 여러 가지 솔루션이 있을 수 있다는 가정을 잊지 않는 것이다.




신고
Trackbacks 3 : Comment 0

Write a comment

티스토리 툴바