| {code} UPDATE 고객 c SET 최종거래일시 = ( SELECT MAX(거래일시) FROM 거래 WHHERE 고객번호 = 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.고객번호 WAND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))) |
|
{color:green} => 한 달 이내 거래가 있던 고객을 두번 조회하는 것으로 변경, 총 고객 수와 한 달 이내 거래가 발생한 고객 수에 따라 성능이 좌우된다.{color}
|
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)))
|
{color:green}=> 총 고객수가 많다면 exists 서브 쿼리를 아래와 같이 해시 세미 조인으로 유도{color}
|
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)))
|
{color:green}=> 한 달 이내 거래를 발생시킨 고객이 많아 update발생량이 많다면 아래와 같이 변경할 수 있으나, 모든 고객 레코드에 lock이 발생하고 이전과 같은 값으로 갱신되는 비중이 높을수록 Redo 로그 발생량이 증가{color}
|
UPDATE 고객 c
SET ( 최종거래일시, 최근거래횟수, 최근거래금액 ) =
( SELECT NVL(MAX(거래일시), c.최종거래일시
, DECODE( COUNT(), 0, c.최근거래횟수, COUNT())
, NVL(SUM(거래금액), c.최근거래금액)
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1)))
|
{color:blue}*다른 테이블과 조인이 필요할때 전통적인 방식의 update문을 사용하면 비효율을 감수해야 한다.{*}{color}
{tip}
set절에 사용된 서브쿼리에는 캐싱 매커니즘이 작용하므로 distinct value 개수가 적은 1쪽 집합을 읽어 M쪽 집합을 갱신할 때 효과적이다.
exists 서브쿼리가 NL세미 조인이나 필터방식으로 처리되는 경우 캐싱 효과가 나타난다.
{tip}
h2. 2. 수정 가능 조인 뷰
{color:blue}*1. 조인뷰는 from절에 두 개 이상 테이블을 가진 뷰를 가리키며, 수정 가능 조인 뷰는 입력, 수정, 삭제가 허용되는 조인 뷰를 말한다.{*}{color}
{color:blue}*2. 1쪽 집합과 조인되는 M쪽 집합에만 입력, 수정, 삭제가 허용된다.{*}{color}
{color:blue}*3. 수정 가능 조인 뷰를 활용하면 전통적인 방식의 update문에서 참조 테이블을 두번 조인하는 비효율을 없앨 수 있다.{*}{color}
|
UPDATE /*+ bypass_ujvc */
( SELECT /*+ ordered use_hash© */
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 최종거래일시 = 거래일시
, 최근거래횟수 = 거래횟수
, 최근거래금액 = 거래금액
|
{color:green} => 1쪽 집합(dept)과 조인되는 M쪽 집합(emp)의 컬럼을 수정하므로 문제가 없어보이나, 수행하면 에러가 발생한다.
=> delete, insert 문도 에러가 발생한다.
=> dept테이블에 unique 인덱스를 생성하지 않았기 때문에 생긴 에러이다.
=> 1쪽 집합에 PK제약을 설정하거나 unique 인덱스를 생성하해야 수정 가능 조인 뷰를 통합 입력, 수정, 삭제가 가능하다.
=> dept테이블에 PK제약을 설정하면 emp 테이블은 키 보존 테이블, dept 테이블은 비 키-보존 테이블이 된다.
{color}
|
SQL> create table emp as select * from scott.emp;
Table created.
SQL> create table dept as select * from scott.dept;
Table created.
SQL> create or replace view EMP_DEPT_VIEW as
2 select e.rowid emp_rid, e.*, d.rowid dept_rid, d.dname, d.loc
3 from emp e, dept d
4 where e.deptno = d.deptno;
View created.
SQL> update EMP_DEPT_VIEW set loc = 'SEOUL' where job = 'CLERK';
update EMP_DEPT_VIEW set loc = 'SEOUL' where job = 'CLERK'
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> select empno, ename, job, sal, deptno, dname, loc
2 from EMP_DEPT_VIEW
3 order by job, deptno;
EMPNO ENAME JOB SAL DEPTNO DNAME LOC
14 rows selected.
SQL> update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;
update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> delete from EMP_DEPT_VIEW where job = 'CLERK';
delete from EMP_DEPT_VIEW where job = 'CLERK'
*
ERROR at line 1:
ORA-01752: cannot delete from view without exactly one key-preserved table
SQL> alter table dept add constraint dept_pk primary key(deptno);
Table altered.
SQL> update EMP_DEPT_VIEW set comm = nvl(comm, 0) + (sal * 0.1) where sal <= 1500;
7 rows updated.
SQL> commit;
Commit complete.
|
h2. 키 보존 테이블이란?
{color:blue}*1. 키 보존 테이블이란, 조인된 결과 집합을 통해서도 중복 값 없이 unique하게 식별히 가능한 테이블{*}{color}
{color:blue}*2. 키 보존 테이블이란, 뷰에 rowid를 제공하는 테이블{*}{color}
{color:green} => EMP_DEPT_VIEW 뷰에서 rowid를 출력해보면, dept_rid에 중복값이 발생하고, emp_rid는 중복값이 없으며 뷰의 rowid와 일치한다.{color}
{color:green} => dept테이블의 unique 인덱스를 제거하면 키 보존 테이블이 없기 때문에 뷰에서 rowid를 출력할 수 없다.{color}
|
SQL> select ROWID, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW;
ROWID EMP_RID DEPT_RID EMPNO DEPTNO
14 rows selected.
SQL> alter table dept drop primary key;
Table altered.
SQL> select rowid, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW;
select rowid, emp_rid, dept_rid, empno, deptno from EMP_DEPT_VIEW
*
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
|
h2. *_UPDATABLE_COLUMNS 뷰 참조
{color:blue}*비 키-보존 테이블로부터 온 컬럼은 입력, 갱신, 삭제가 허용되지 않으며, *_UPDATABLE_COLUMNS 뷰를 통해 확인 할 수 있다.{*}{color}
|
SQL> alter table dept add constraint dept_pk primary key(deptno);
Table altered.
SQL> insert into EMP_DEPT_VIEW
2 (empno, ename, job, mgr, hiredate, sal, comm, deptno, loc)
3 select empno, ename, job, mgr, hiredate, sal, comm, deptno, loc
4 from EMP_DEPT_VIEW;
(empno, ename, job, mgr, hiredate, sal, comm, deptno, loc)
*
ERROR at line 2:
ORA-01776: cannot modify more than one base table through a join view
SQL> select column_name, insertable, updatable, deletable
2 from user_updatable_columns
3 where table_name = 'EMP_DEPT_VIEW';
COLUMN_NAME INS UPD DEL
12 rows selected.
SQL> insert into EMP_DEPT_VIEW
2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
3 select empno, ename, job, mgr, hiredate, sal, comm, deptno
4 from EMP_DEPT_VIEW;
14 rows created.
SQL> commit;
Commit complete.
|
h2. 수정 가능 조인 뷰 제약 회피
{color:blue}*bypass_ujvc 힌트는 키 보존 테이블이 없더라도 update 수행이 가능하게 하는 힌트이다.*{color}
{color:blue}*update를 위해 참조하는 집합에 중복 레코드가 없을 때만 이 힌트를 사용해야 한다.*{color}
{color:green}=> emp테이블에서 deptno로 group by한 결과는 unique하기 때문에 이 집합과 조인되는 dept 테이블은 키가 보존됨에도 에러가 발생한다.{color}
|
SQL> alter table dept add avg_sal number(7,2);
Table altered.
SQL> update
2 (select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
3 from (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
4 , dept d
5 where d.deptno = e.deptno )
6 set d_avg_sal = e_avg_sal ;
set d_avg_sal = e_avg_sal
*
ERROR at line 6:
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> update /*+ bypass_ujvc */
2 (select d.deptno, d.avg_sal d_avg_sal, e.avg_sal e_avg_sal
3 from (select deptno, round(avg(sal), 2) avg_sal from emp group by deptno) e
4 , dept d
5 where d.deptno = e.deptno )
6 set d_avg_sal = e_avg_sal ;
3 rows updated.
SQL> select * from dept;
DEPTNO DNAME LOC AVG_SAL
|
h1. 2. Merge문 활용
{color:blue}*DW에서 데이터 적재 작업을 효과적으로 지원하게 위해 오라클 9i부터 merge into 문을 지원.*{color}
1. 전일 발생한 변경 데이터를 기간계 시스템으로 부터 추출 (Extraction)
2. customer_delta 테이블을 DW시스템으로 전송 (Transportation)
3. DW 시스템으로 적재 (Loading)
|
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, ...
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);
|
h2. Optional Clauses
{color:green} 10g부터는 update와 insert를 선택적으로 처리할 수 있다.{color}
|
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);
|
{color:green}=> merge문으로 수정 가능 조인 뷰의 기능을 대체.{color}
|
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;
|
h2. Conditional Operations
{color:green}10g에서는 on절에 기술한 조인문외에 추가로 조건절을 기술할 수 있다.{color}
|
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_char('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) ;
|
h2. DELETE Clauses
{color:green}10g에서는 merge문을 이용하여 이미 저장된 데이터를 조건에 따라 지울 수 있다.{color}
{color:green}=> update가 이루어진 결과로서 ?퇴일자가 null이 아닌 레코드만 삭제된다. 탈퇴일자가 null이 아니었어도 merge문을 수행한 결과가 null이면 삭제되지 않는다.{color}
|
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);
|
h2. Merge Into 활용
{color:blue}*1. SQL 수행 빈도 개선*{color}
저장하려는 레코드가 기존에 있던 것이면 update를 수행하고, 그렇지 않으면 insert를 수행하는 경우, SQL이 항상 두번씩 수행된다. (select 한번, insert 또는 update 한 번)
merger문을 활용하면 SQL이 한번만 수행된다
{color:blue}*2. 논리I/O발생을 감소하여 SQL 수행 속도 개선*{color}
( 참고 : 298page ~ )
h1. 3. 다중 테이블 Insert 활용
{color:blue}*오라클 9i부터는 조건에 따라 여러 테이블에 insert하는 다중 테이블 insert문을 제공한다.*{color}
|
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;
|
{color:green}=> 다중 테이블 insert문을 활용하면 대용량 거래당사자 테이블을 한 번만 읽고 처리할 수 있다.{color}
|
INSERT FIRST
WHEN 구분 = 'A' THEN
INTO 청구보험당사자 ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
VALUES ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
WHEN 구분 = 'B' THEN
INTO 자동차사고접수당사자 ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
VALUES ( 당사자ID, 접수일자, 접수순번, 담보구분, 청구순번, ...)
SELECT 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;
|
h2. 문서에 대하여
* 최초작성자 : [~kwlee55]
* 최초작성일 : 2010년 04월 08일
* 이 문서는 [오라클클럽|http://www.gurubee.net] [코어 오라클 데이터베이스 스터디|4차 코어 오라클 데이터베이스 스터디] 모임에서 작성하였습니다.
* {color:blue}{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*{color}