오라클 성능 고도화 원리와 해법 II (2010년)
목록
조인을 내포한 DML 튜닝 0 0 4,736

by 구루비 조인원리 BYPASS_UJVC MERGE 키보존테이블 [2010.04.08]

I. 수정 가능 조인 뷰 활용

1. 전통적인 방식의 UPDATE

{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

--
--
--
--
--
--
--
7902 FORD ANALYST 3000 20 RESEARCH DALLAS
7788 SCOTT ANALYST 3000 20 RESEARCH DALLAS
7934 MILLER CLERK 1300 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 800 20 RESEARCH DALLAS
7876 ADAMS CLERK 1100 20 RESEARCH DALLAS
7900 JAMES CLERK 950 30 SALES CHICAGO
7782 CLARK MANAGER 2450 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 2975 20 RESEARCH DALLAS
7698 BLAKE MANAGER 2850 30 SALES CHICAGO
7839 KING PRESIDENT 5000 10 ACCOUNTING NEW YORK
7654 MARTIN SALESMAN 1250 30 SALES CHICAGO
7844 TURNER SALESMAN 1500 30 SALES CHICAGO
7521 WARD SALESMAN 1250 30 SALES CHICAGO
7499 ALLEN SALESMAN 1600 30 SALES CHICAGO

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

--
--
--
--
--
AAAitBAAZAAADf8AAA AAAitBAAZAAADf8AAA AAAitCAAZAAADgEAAB 7369 20
AAAitBAAZAAADf8AAB AAAitBAAZAAADf8AAB AAAitCAAZAAADgEAAC 7499 30
AAAitBAAZAAADf8AAC AAAitBAAZAAADf8AAC AAAitCAAZAAADgEAAC 7521 30
AAAitBAAZAAADf8AAD AAAitBAAZAAADf8AAD AAAitCAAZAAADgEAAB 7566 20
AAAitBAAZAAADf8AAE AAAitBAAZAAADf8AAE AAAitCAAZAAADgEAAC 7654 30
AAAitBAAZAAADf8AAF AAAitBAAZAAADf8AAF AAAitCAAZAAADgEAAC 7698 30
AAAitBAAZAAADf8AAG AAAitBAAZAAADf8AAG AAAitCAAZAAADgEAAA 7782 10
AAAitBAAZAAADf8AAH AAAitBAAZAAADf8AAH AAAitCAAZAAADgEAAB 7788 20
AAAitBAAZAAADf8AAI AAAitBAAZAAADf8AAI AAAitCAAZAAADgEAAA 7839 10
AAAitBAAZAAADf8AAJ AAAitBAAZAAADf8AAJ AAAitCAAZAAADgEAAC 7844 30
AAAitBAAZAAADf8AAK AAAitBAAZAAADf8AAK AAAitCAAZAAADgEAAB 7876 20
AAAitBAAZAAADf8AAL AAAitBAAZAAADf8AAL AAAitCAAZAAADgEAAC 7900 30
AAAitBAAZAAADf8AAM AAAitBAAZAAADf8AAM AAAitCAAZAAADgEAAB 7902 20
AAAitBAAZAAADf8AAN AAAitBAAZAAADf8AAN AAAitCAAZAAADgEAAA 7934 10

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

-- --- --- ---
EMP_RID YES YES YES
EMPNO YES YES YES
ENAME YES YES YES
JOB YES YES YES
MGR YES YES YES
HIREDATE YES YES YES
SAL YES YES YES
COMM YES YES YES
DEPTNO YES YES YES
DEPT_RID NO NO NO
DNAME NO NO NO
LOC NO NO NO

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

--
--
--
--
10 ACCOUNTING NEW YORK 2916.67
20 RESEARCH DALLAS 2175
30 SALES CHICAGO 1566.67
40 OPERATIONS BOSTON

|

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}
"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3213

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입