(1) 수정 가능 조인 뷰 활용

• 전통적인 방식의 UPDATE

구 분	S Q L
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)))
변경 후 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)));

  ☞ 변경 후에도 고객을 두 번 조회하기 때문에 비효율이 남아있음

      총 고객수와 한 달 이내 거래가 발생한 고객 수에 따라 성능이 차이남

  ☞ 총 고객 수가 아주 많을 시 Exists 서브쿼리를 해시 세미 조인으로 유도하는 방법 고려

where

exists   (select /*+ unnest hash_sj */ 'X' from 거래               where 고객번호 = c.고객번호                  and 거래일시 >= trunc(add_months(ssydate, -1)));

 ☞ 한 달 이내 거래 발생 고객이 많아 update 발생이 많을 시 다음과 같은 방법 고려

set

(최종거래일시, 최근거래횟수, 최근거래금액) =  (select nvl (max(거래일시),c.최종거래일시)                                                                            , decode(count(*), 0, c.최근거래횟수, count(*))                                                                           , nvl (sum(거래금액), c.최근거래금액)                                                                     from   거래                                                                           where 고객번호 = c.고객번호                                                                        and 거래일시 >= trunc(add_months(ssydate, -1)))

        → 모든 고객 레코드에 lock 발생, 이전 값과 동일 한 값으로 갱신 되는 비중이 높을 수록 Redo 로그 발생량 증가로 역효과

※ 다른 테이블과 조인이 필요할 때 전통적 방식의 update 문 사용 시 비효율은 감수해야 함

cf.) set 절에 사용된 서브쿼리에는 캐싱 메커니즘이 작용하여 

              distinct value 개수가 적은 한쪽 집합을 읽어 

              exists 서브쿼리가 NL 세미 조인이나 필터방식 처리 시에도 캐싱 효과 발생

• 수정 가능 조인 뷰  : 수정 가능 조인 뷰 활용 시 참조 테이블과 두 번 조인하는 비효율 無

  UPDATE /*+ bypass_ujvc */

  (SELECT /*+ ORDERED USE_HASH(C)*/                      

         C.최종거래일시, C.최근거래횟수, C.최근거래금액

       , T.거래일시, T.거래횟수, T.거래금액 

   FROM   (SELECT 고객번호, MAX(거래일시) 거래일시, COUNT(*) 거래횟수, SUM(거래금액) 거래금액

           FROM   거래 A                                                                     

           WHERE  거래일시 >= TRUNC( ADD_MONTHS( SYSDATE, -1))                               

           GROUP BY 고객번호) T, 고객 C          

   WHERE  C.고객번호 = T.고객번호                                                            

  )

  SET 최종거래일시 = 거래일시

    , 최근거래횟수 = 거래횟수

    , 최근거래금액 = 거래금액;

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

   결과 예측 > job = 'CLERK' 인 사원이 10, 20, 30 부서에 모두 속해 있기 때문에,

                    세 부서(10, 20, 30)의 소재지가 모두 'SEOUL' 바뀜

   

   사용자 의도 > 다른 job을 가진 사원의 부서 소재지까지 바뀌는 것은 원하는 결과가 아닐 수 있음

select empno, ename, job, sal, deptno, dname, loc 

from   EMP_DEPT_VIEW

order by job, deptno;

       ◆ Test 2 > 조인 뷰를 통해 Update : 한 쪽 집합(dept)과 조인되는 M쪽 집합(emp) 컬럼 수정

update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;

delete from EMP_DEPT_VIEW where job = 'CLERK';

    ☞ error 발생 : 한 쪽 집합에 PK 제약을 설정하거나 unique 인덱스를 생성해야 수정 가능 조인 뷰를 통한 DML 가능

alter table dept add constraint dept_pk primary key(deptno);

update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;

→ PK 제약을 설정 시, emp 테이블은 '키-보존 테이블(Key-Preserved Table)'

 dept 테이블은 '비 키-보존 테이블(Non Key-Preserved Table)'

• 키 보존 테이블이란? 

  : 조인된 결과 집합을 통해서도 중복 값 없이 Unique 하게 식별 가능한 테이블

select ROWID, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW;

☞ 키 보존 테이블인 emp 테이블의 rid에는 중복 값이 없고 뷰의 rowid와 일치,

                    비 키 보존 테이블인 dept 테이블의 rid에는 중복 값 발생

                    → '키 보존 테이블' = 뷰에 rowid를 제공하는 테이블

alter table dept drop primary key;

select rowid, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW; 

☞ error 발생 : dept 테이블의 unique 인덱스를 제거 시, 키 보존 테이블이 없기 때문에 뷰에서 rowid를 출력 할 수 없음

• *_UPDATABLE_COLUMNS 뷰 참조 

• 수정 가능 조인 뷰를 통한 insert 시도

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;

☞ error 발생 

• *_updatable_columns 뷰를 통해 확인

select column_name, insertable, updatable, deletable

from   user_updatable_columns

where  table_name = 'EMP_DEPT_VIEW';

☞ dept 테이블의 DEPT_RID, DNAME, LOC 컬럼의 insertable, updatable, deletable 값이 각 각 NO로 표시

• 비 키-보존 테이블의 컬럼(loc) 제외한 insert

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;

☞ insert 적용 ok

• 수정가능 조인 뷰 제약 회피

• 부서별 평균 급여 저장 컬럼 dept 테이블에 추가, emp 테이블에서 집계값을 추출하여 적용하는 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 ;

☞ error 발생   

• bypass_ujvc 힌트 사용 : Update Join View Check 생략

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;

※ update를 위해 참조하는 집합에 중복 레코드가 없을 때만 사용!!

(2) Merge문 활용

• 두 시스템 간 데이터 동기화 작업 시 merge into 문 활용 (9i 부터 지원)

1. 변경데이터 추출 (Extraction)

create table customer_delta

as

select * from customer

where mod_dt between trunc(sysdate) - 1 and trunc(sysdate) - 1/86400;

2. DW 시스템으로 전송 (Transoportation)

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

• 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_ujvc 힌트 대신 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 : 10g에서 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 문 사용 시, 디스크 I / O가 발생되지 않았음에도, 수천만 개의 논리 I/O 발생으로 8분 7초 소요

☞ merge 문 사용 시 1.16초만에 수행

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

when 구분 = 'A' then

into 청구보험당사자(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)

values(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)

when 구분 = 'B' then

into 자동차사고접수당사자(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)

values(당사자id, 접수일자, 접수순번, 담보구분, 청구순번, ...)

select a.구분, a.당사자id, a.접수일자, a.접수순번, a.담보구분, a.청구순번, ...

from (

select 'A' 구분, ...

from   청구보험당사자_임시

union all

select 'B' 구분, ...

from   가사고접수당사자_임시

where 당사자구분 not in  ('4','5','6')

) a, 거래당사자 b

where a.당사자id = b.당사자id;

☞ 다중 테이블 insert문을 활용하면 대용량 거래당사자 테이블을 한 번만 읽고 처리

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 위충환 (실천하자)
  
• 최초작성일: 2011년 04월 03일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^