여러 개의 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.
역시 가장 중요한 것은 하나의 문제에 대해 여러 가지 솔루션이 있을 수 있다는 가정을 잊지 않는 것이다.
출처 : http://ukja.tistory.com/187
