h5.(1)수정 가능 조인 뷰 활용
전통적인 방식의 UPDATE
update 고객 C
set 최종거래일시 = (select max(거래일시) from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
, 최근거래횟수 = (select count(*) from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
, 최근거래금액 = (select sum(거래금액) from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
where exists (select 'X' from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
<수정후>
update 고객 C
set (최종거래일시, 최근거래횟수, 최근거래금액) =
(select max(거래일시), count(*), sum(거래금액)
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
where exists ( select 'X'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
한달 이내 거래가 있던 고객을두 번 조회하기 때문인데, 총고객 수와한달 이내 거래가발생한고객 수에 따라 성능이 좌우된다.
총 고객 수가 아주 많다면 Exists 서브쿼리를 아래와 같이 해시 세미 조인으로 유도하는 것을 고려
update 고객 C
set (최종거래일시, 최근거래횟수, 최근거래금액) =
(select max(거래일시), count(*), sum(거래금액)
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
where exists (select /*+ unnest hash_sj */ 'X' from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
한 달 이내 거래를 발생시킨 고객이 많아 update 발생량이 많다면 아래와 같이 변경하는것을 고려
하지만 모든 고객 레코드에 lock이 발생함은 물론 이전과 같은 값으로 갱신 되는 비중이 높을수록 Redo 로그 발생량이 증가해 오히려 비효율적일 수 있다.
update 고객 C
set (최종거래일시, 최근거래횟수, 최근거래금액) =
(select nvl(max(거래일시), c.최종거래일시)
, decode(count(*), 0, c.최근거래횟수, count(*))
, nvl(sum(거래금액), c.최근거래금액)
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(add_months(sysdate, -1)))
다른 테이블과 조인이 필요할 때 전통적인 방식의 update문을 사용하면 비효율을 감수.
set절에 사용된 서브쿼리에는 캐싱 메커니즘이 작용하므로 distinct value 개수가 적은 1쪽집합을 읽어 M쪽 집합을 갱신할 때 효과적.
exists 서브쿼리가 NL 세미 조인이나 필터방식으로 처리된다면 거기서도 캐싱 효과 발생.
h5.수정 가능 조인뷰
update /*+ bypass_ujvc */
( select /*+ ordered use_has(c) */
c.최종거래일시, c.최근거래횟수, c.최근거래금액
,t.거래일시, t.거래횟수, t.거래금액
from (select 고객, max(거래일시) 거래일시, count(*) 거래횟수, sum(거래금액) 거래금액
from 거래
where 거래일시 >= trunc(add_months(sysdate,-1))
group by 고객) t
, 고객 c
where c.고객번호 = t.고객번호
)
set 최종거래일시 = 거래일시
, 최근거래횟수 = 거래횟수
, 최근거래금액 = 거래금액
'조인 뷰'는 from절에 두 개 이상 테이블을 가진 뷰를 가리키며, '수정 가능 조인 뷰(updatable/modifiable join view)'는 말 그대로 입력, 수정, 삭제가 허용되는 조인 뷰를 말한다.
h5.(2)Merge문 활용
MERGE INTO [1. 테이블 명 혹은 VIEW명] - Update또는 Insert할 테이블 명 혹은 뷰
USING [2. 조회서브쿼리] --(만약 INTO절의 동일 테이블이라면 dual 사용)
ON [1과2의 조인 조건] - 조인 조건의 KEY와 일치여부[UPDATE/INSERT 조건은 바로 ON절에 의해 결정]
WHEN MATCHED THEN -일치되는 경우 UPDATE
UPDATE SET -- ※조인조건(on)절에 사용한 컬럼은 UPDATE가 불가하다!!
[컬럼1] = [값1],
[컬럼2] = [값2]
...
WHEN NOT MATCHED THEN -일치 안 되는 경우 INSERT
INSERT(컬럼1, 컬럼2...)
VALUES(값1, 값2...)
MERGE INTO
GHT Y
USING (
SELECT IYYYY YYYY
, IMM MM
, '00' DD
, A.MEDEPT DEPT
, COUNT(*) VAL01
FROM ABC A
, DEF B
WHERE A.DATE BETWEEN TO_DATE('20170301','YYYYMMDD') AND LAST_DAY(TO_DATE('20170301','YYYYMM'))
) X
ON (
X.YYYY = Y.YYYY AND
X.MM = Y.MM AND
X.DD = Y.DD AND
X.DEPT = Y.DEPT AND
)
WHEN MATCHED THEN
UPDATE
SET
Y.VAL01 = X.VAL01
WHEN NOT MATCHED THEN
INSERT (
YYYY
,MM
,DD
,DEPT
,VAL01
)
VALUES (
X.YYYY
,X.MM
,X.DD
,X.DDEPT
,X.VAL01
);
h5.(3)다중 테이블 Insert 활용
9i부터 여러 테이블에 insert하는 '다중 테이블 insert문' 기능 제공.
insert into 청구보험당사자(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)
select ...
from 청구보험당사자_임시 a, 거래당사자 b
where a.당사자id, = b.당사자id;
insert into 자동차사고접수당사자(당사자id, 접수일자, 접수순번, 담보구분,청구순번, ...)
select ...
from 가사고접수당사자_임시 a, 거래당사자 b
where b.당사자구분 not in ('4','5','6')
and a.당사자id = b.당사자id;
청구보험당사자_임시와 가사고접수당사자_임시는 10만건 미만이지만 거래 당사자는 수천만 건에 이르는 대용량 테이블일 경우,
아래와 같이 다중 테이블 insert문을 활용하면 대용량 거래당사자 테이블을 한 번만 일고 처리 가능.
insert first
when 구분 = 'A' then
into 청구보험당사자(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)
values(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)
when 구분 = 'B' then
into 자동차사고접수당사자(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)
values(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)
from (
select 'A' 구분, ...
from 청구보험당사자_임시
union all
select B' 구분, ...
from 가사고접수당사자_임시
where 당사자구분 not in ('4','5','6')
) a, 거래당사자 b
where a.당사자id = b.당사자id