메뉴 건너뛰기

bysql.net

7. 조인을 내포한 DML 튜닝

2011.04.04 08:54

오예스 조회 수:9943

07 조인을 내포한 dml 튜닝

(1) 수정 가능 조인 뷰 활용.
전통적인 방식의 UPDATE
update 고객 C
set 최종거래일시 = (select max(거래일시) from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)))
, 최근거래횟수 = (select count(*) from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)))
, 최근거래금액  = (select sum(거래금액) from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)))
where exists   (select 'X' from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)));
=>
update 고객 C
set (최종거래일시, 최근거래횟수, 최근거래금액) = (select max(거래일시), count(*), sum(거래금액) from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)))
where exists   (select 'X' from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)));

위와 같이 변경해도 고객을 두 번 조회하기 때문에 비효율 발생.
상황1. 총 고객 수가 아주 많다면 Exists 서브쿼리를 아래와 같이 해시 세미 조인으로 유도.
update 고객 C
set (최종거래일시, 최근거래횟수, 최근거래금액) = (select max(거래일시), count(*), sum(거래금액) from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)))
where exists   (select /*+ unnest hash_sj */ 'X' from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)));

상황2. 한 달 이내 거래를 발생시킨 고객이 많아 update 발생량이 많다면 아래와 같이 변경하는것을 고려.
update 고객 C
set (최종거래일시, 최근거래횟수, 최근거래금액) = (select nvl(max(거래일시), c.최종거래일시)
                                                    , decode(count(*), 0, c.최근거래횟수, count(*))
                                                    , nvl(sum(거래금액), c.최근거래금액)
                    from 거래
                   where 고객번호 = c.고객번호
                     and 거래일시 >= trunc(add_months(ssydate, -1)));

전통적인 방식의 UPDATE 정리:
위 방식들처럼 다른 테이블과 조인이 필요할 때 전통적인 방식의 update문을 사용하면 비효율을 감수.

참고로, set절에 사용된 서브쿼리에는 캐싱 메커니즘이 작용하므로 distinct value 개수가 적은 1쪽집합을 읽어 M쪽 집합을 갱신할 때 효과적.

exists 서브쿼리가 NL 세미 조인이나 필터방식으로 처리된다면 거기서도 캐싱 효과 발생.

수정 가능 조인뷰
아래와 같이 수정 가능 조인 뷰를 활용하면 참조 테이블과 두 번 조인하는 비효율을 없앨 수  있다.
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)'는 말 그대로 입력,
수정, 삭제가 허용되는 조인 뷰를 말한다. 단 1쪽 집합과 조인되는 M쪽 집합에만 입력, 수정, 삭제가 허용된다.

아래와 같이 생성한 조인 뷰를 통해 job = 'CLERK'인 레코드의 loc를 모두 'SEOUL'로 변경하는 것을 허용하면 어떤 일이 발생할까?

-- 수정 가능 조인 뷰
create table emp  as select * from scott.emp;
create table dept as select * from scott.dept;
create or replace view EMP_DEPT_VIEW as
select e.rowid emp_rid, e.*, d.rowid dept_rid, d.dname, d.loc
from emp e, dept d
where  e.deptno = d.deptno;
update EMP_DEPT_VIEW set loc = 'SEOUL' where job = 'CLERK';

-- 키 보존 테이블이란?  뷰에 rowid를 제공하는 테이블.
select ROWID, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW;
alter table dept drop primary key;
select rowid, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW;

-- *_UPDATABLE_COLUMNS 뷰 참조
alter table dept add constraint dept_pk primary key(deptno);
insert into EMP_DEPT_VIEW
    (empno, ename, job, mgr, hiredate, sal, comm, deptno, loc)
select empno, ename, job, mgr, hiredate, sal, comm, deptno, loc
from EMP_DEPT_VIEW;

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

insert into EMP_DEPT_VIEW
    (empno, ename, job, mgr, hiredate, sal, comm, deptno)
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from EMP_DEPT_VIEW;
commit;

-- 수정가능 조인 뷰 제약 회피. bypass_ujvc 힌트역할 Updatable Join View Check를 생략하라고 옵티마이저에게 지시하는 힌트
  (update를 위해 참조하는 집합에 중복 레코드가 없을 때만 이 힌트를 사용)

alter table dept add avg_sal number(7,2);
update
(select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
from  (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
    , dept d
where  d.deptno = e.deptno )
set d_avg_sal = e_avg_sal ;

update /*+ bypass_ujvc */
(select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
from  (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
    , dept d
where  d.deptno = e.deptno )
set d_avg_sal = e_avg_sal ;
select * from dept;

(2) Merge문 활용
DW 오퍼레이션(자주 발생하는) : 기간계 시스템에서 가져온 신규 트랜잭션 데이터를 반영함으로써 두 시스템간 데이터를 동기화시키는 작업.
예를 들어, 고객(Customer) 테이블에 발생한 변경분 데이터를 DW에 반영하는 프로세스는 다음과 같다. 이중에서 3번 데이터 적재 자업을 효과적으로
지원하기 위애 오라클 9i부터 merge into 문을 지원하기 시작.

추출(Extraction) > 전송(Transportation) > 적재(Loading)

1. 전일 발생한 변경 데이터를 기간계 시스템으로부터 추출(Extraction)
create table customer_delta
as
select * from customer
where mod_dt between trunc(sysdate) - 1 and trunc(sysdate) - 1/86400;
2. cmstomer_delta 테이블을 DW시스템으로 전송(Transportation)
3. DW 시스템으로 적재(Loading)
merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
where matched then update
set t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...
when not matched then insert
(cust_id, cust_nm, email, tel_no, region, addr, reg_dt) values
(s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt);

10g에서 추가된 merge into문 기능.

Optional Clauses
10g에서는 아래와 같이 update와 insert를 선택적으로 처리가능.

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then update
set t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...;

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when not matched then insert
(cust_id, cust_nm, email, tel_no, region, addr, reg_dt) values
(s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt);

이 확장 기능을 통해 Updatable Join View 기능을 대체할 수 있게 되었다. 즉, bypass_ujc 힌트가
필요할때 아래와 같이 merge문으로 처리 가능.
merge into dept d
using (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
on (d.deptno = e.deptno)
when matched then update set d.avg_sal = e.avg_sal;

Conditinal Operations
10에서는 on절에 기술한 조인문 외에 아래와 같이 추가로 조건절 기술 가능.

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then update
set t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...
where reg_dt >= to_date('20000101', 'yyyymmdd')
when not matched then insert
(cust_id, cust_nm, email, tel_no, region, addr, reg_dt) values
(s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt)
where reg_dt < trunc(sysdate);

DELETE Clause
10g에서 제공되기 시작한 또 한 가지 확장 기능은, 이미 저장된 데이터를 조건에 따라 지우는 것이다.

merge into customer t using customer_delta s on (t.cust_id = s.cust_id)
when matched then update
set t.cust_id = s.cust_id, t.cust_nm = s.cust_nm, t.email = s.email, ...
delete where t.withdraw_dt is not null -- 탈퇴일시가 null이 아닌 레코드 삭제.
when not matched then insert
(cust_id, cust_nm, email, tel_no, region, addr, reg_dt) values
(s.cust_id, s.cust_nm, s.email, s.tel_no, s.region, s.addr, s.reg_dt);

기억할 점은, 예시한 merge문에서 update가 이루어진 결과로서 탈퇴일시(withdraw_dt)가 null이 아닌 레코드만 삭제된다는 사실이다.
즉, 탈퇴일시가 null이 아니었어도 merge문을 수행한 결과가 null이면 삭제되지 않는다.

Merge into 활용 -1
작업사항 : 저장하려는 레코드가 기존에 있던것이면 update. 그렇지 않으면 insert

항상두번씩 실행 sql(select 한 번, insert 또는 update 한 번)
select count(*) into :cnt from dept where deptno = :val1;
if :cnt = 0 then
insert into dept(deptno, dname, loc) values(:val1, :val2, :val3);
else
update dept set dname = :val2, loc= :val3 where deptno = :val;
end if;

최대 두 번  실행 sql
update dept set dname = :val2, loc = :val3 where deptno = :val1;
if sql%rowcount = 0 then
insert into dept(deptno, dname, loc) values(:val1, :val2, :val3);
end if;

한 번 실행 sql
merge into dept a
using (select :val1 deptno, :val2 dname, :val3 loc from dual) b
on (b.deptno = a.deptno)
when matched then
update set dname = b.dname, loc= b.loc
when not matched then
insert (a.deptno, a.dname, a.loc) values (b.deptno, b.dname, b.loc);

Merge into 활용 -2
청구일반내역 테이블의 인덱스 구성.
청구일반내역_PK : 청구년월 + 청구생성번호 + 일련번호
청구일반내역_X01 : 청구년월 + 청구생성번호 + 청구일련번호 + 오류내역
update 청구일반내역 => merge into 청구일반내역 으로 변경시 논리 I/O 감소.
가장 큰원인은 청구일반내역_PK 인덱스의 차이.

(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;