Oracle Join Update 정리 1
Updated:
예제 테이블 생성
가. EMP 테이블 생성
오라클 11G DBMS를 설치하면 샘플 유저인 SCOTT 유저가 설치되는데 EMP 라는 테이블을 기준으로 테스트해본다.
| Empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980/12/17 00:00:00 | 800 | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 1981/02/20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981/02/22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981/04/02 00:00:00 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1981/09/28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981/05/01 00:00:00 | 2850 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 1981/06/09 00:00:00 | 2450 | 10 | |
| 7788 | SCOTT | ANALYST | 7566 | 1987/04/19 00:00:00 | 3000 | 20 | |
| 7839 | KING | PRESIDENT | 1981/11/17 00:00:00 | 5000 | 10 | ||
| 7844 | TURNER | SALESMAN | 7698 | 1981/09/08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987/05/23 00:00:00 | 1100 | 20 | |
| 7900 | JAMES | CLERK | 7698 | 1981/12/03 00:00:00 | 950 | 30 | |
| 7902 | FORD | ANALYST | 7566 | 1981/12/03 00:00:00 | 3000 | 20 | |
| 7934 | MILLER | CLERK | 7782 | 1982/01/23 00:00:00 | 1300 | 10 |
- 생성 스크립트
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES
(7369, ‘SMITH’, ‘CLERK’, 7902,
TO_DATE(‘17-12-1980’, ‘DD-MM-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES
(7499, ‘ALLEN’, ‘SALESMAN’, 7698,
TO_DATE(‘20-02-1981’, ‘DD-MM-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES
(7521, ‘WARD’, ‘SALESMAN’, 7698,
TO_DATE(‘22-02-1981’, ‘DD-MM-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES
(7566, ‘JONES’, ‘MANAGER’, 7839,
TO_DATE(‘02-04-1981’, ‘DD-MM-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES
(7654, ‘MARTIN’, ‘SALESMAN’, 7698,
TO_DATE(‘28-09-1981’, ‘DD-MM-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES
(7698, ‘BLAKE’, ‘MANAGER’, 7839,
TO_DATE(‘01-05-1981’, ‘DD-MM-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES
(7782, ‘CLARK’, ‘MANAGER’, 7839,
TO_DATE(‘09-06-1981’, ‘DD-MM-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES
(7788, ‘SCOTT’, ‘ANALYST’, 7566,
TO_DATE(‘09-12-1982’, ‘DD-MM-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7839, ‘KING’, ‘PRESIDENT’, NULL,
TO_DATE(‘17-11-1981’, ‘DD-MM-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES
(7844, ‘TURNER’, ‘SALESMAN’, 7698,
TO_DATE(‘08-09-1981’, ‘DD-MM-YYYY’), 1500, NULL, 30);
INSERT INTO EMP VALUES
(7876, ‘ADAMS’, ‘CLERK’, 7788,
TO_DATE(‘12-01-1983’, ‘DD-MM-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES
(7900, ‘JAMES’, ‘CLERK’, 7698,
TO_DATE(‘03-12-1981’, ‘DD-MM-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES
(7902, ‘FORD’, ‘ANALYST’, 7566,
TO_DATE(‘03-12-1981’, ‘DD-MM-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES
(7934, ‘MILLER’, ‘CLERK’, 7782,
TO_DATE(‘23-01-1982’, ‘DD-MM-YYYY’), 1300, NULL, 10);
나. DEPT 테이블 생성
DEPT 테이블은 EMP테이블의 부모테이블이고, DEPTNO가 PK이며 EMP테이블의 DEPTNO 칼럼이 FK로 연결됩니다.
| DEPTNO | DNAME | LOC |
|---|---|---|
| 10 | ACCOUNTING} | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO DEPT VALUES (20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO DEPT VALUES (30, ‘SALES’, ‘CHICAGO’);
INSERT INTO DEPT VALUES (40, ‘OPERATIONS’, ‘BOSTON’);
1. 조인 업데이트 기본 개념
오라클 조인 업데이트는 2개이상의 테이블을 서로 연결하여 원하는 값으로 타겟 테이블의 값을 변경하는 방법을 이야기하겠습니다. 그런데 사용할때 기본적인 문법이 ANSI SQL의 형태와 다르며, 최고의 성능을 내기 위해서는 원쿼리형태의 업데이트 문장을 만들어 튜닝하여 사용하여야 하며 어떤 BUG나 문제가 잘 발생하는지 이해하여만 좋은 업데이트 문장을 만들수 있다. 조인업데이트 관련해서는 UPDATE문장 2가지 방법, MERGE문장, 루핑형 SQL을 사용하여 데이타를 PL/SQL로 처리하는 방법등 4가지 형태로 장단점과 사용시기를 설명하겠습니다.
가. MS SQL 에서의 조인 업데이트 문장
아래의 SQL을 봅니다. 이런 형태의 SQL은 MSSQL에서는 사용 가능하나, 안타깝게도 오라클에서는 사용할수 없다. 오라클에서의 조인 업데이트는 3가지 형태로 구분하여 사용가능한데 아래의 SQL문장을 보고 주요 장단점과 사용방법을 익혀서 실전에 사용해봅니다.
Update A_TableName Set Column = B.Column
From A_TableName as A Inner join
B_TableName as B
ON A.keyColumn = B.keyColumn
Where A.Column <> B.Column;
나. 오라클 조인 업데이트 문장 1
가장 기본적인 형태의 업데이트 문장중 하나입니다. SET 칼럼에 서브쿼리를 넣는데 = 로 조인되어 HASH, NL(Nested Loop) 조인이 안되고 오로지 필터처리만 가능하겠습니다. 필터처리방식은 소량의 데이타를 처리할때에는 장점이 있지만 다량의 데이타를 처리할때에는 성능문제가 발생하겠습니다.(해쉬, NL조인이 안되기 때문에 다량의 데이타를 처리할때(보통 수십만건이상)는 속도가 저하되는 현상을 보인다.) 그러나 만일 WHERE절을 넣을수 있는 업무 조건이 됩니다면 해쉬, NL조인이 가능하므로 2번째 방법이나, MERGE와 똑같은 속도를 보장 받을수 있을것입니다. 결국 이 방식은 몇만건이하의 데이타를 처리할때 사용하는 방식(하드웨어 스펙 고려)이며, 데이타가 많을떄는 아래에서 소개하는 2번째 방법이나 3번째인 MERGE를 사용하여 해쉬조인 처리하는것이 속도가 훨씬 좋을것으로 생각됩니다.
UPDATE Table a –-SET절까지만 있다면 hash, NL조인 안되고 필터처리만 됨
SET a.column = (SELECT RefColumn
FROM TABLE b
WHERE b.key = a.key) ;
--WHERE a.column in (select ------- ) ;
--WHERE절을 넣을수 있는 업무조건이라면 HASH, Nested Loop조인도 가능하다.
다. 오라클 조인 업데이트 문장 2
아래의 업데이트 형태는 NL조인이나 HASH조인 둘다 가능하여 소량이나 다량의 데이타를 처리하는데 문제가 없는 문장형태입니다. /+bypass_ujvc/ 힌트가 들어있는데 이 힌트는 11G DB부터 없어진 힌트로 차후에 설명하도록 하겠습니다.
UPDATE /*+bypass_ujvc*/ --이 힌트는 11g에서 사용할수 없음. 해쉬, NL조인 가능
(SELECT A.Column, B.RefColumn --바깥쪽에서 조인시 필요한 칼럼을 전부 가져온다
FROM Table A, Table B
WHERE A.keyColumn = B.keyColumn)
SET Column = RefColumn ;
라. 오라클 조인 업데이트 문장 3
오라클 Merge문장은 향후 따로 설명하겠지만 굉장히 강한 기능을 가지고 있다. 웬만큼 튜닝을 하면 속도보장이 되며 Wrong Results(잘못된값 처리)도 거의 없는 sql형태입니다. Merge문장은 NL, HASH등 여러가지 조인을 사용할수 있고 업무요건이 복잡한 IF.. THEN.. ELSE 구조의 업무처리 방식을 원쿼리형태의 SQL로 쉽게 만들수 있도록 해준다. 실전현장에서 3시간동 수행되는 루핑형SQL 형태의 UPDATE문장을 MERGE로 수정후 단 몇초 ~ 몇분사이에 수행된 경우도 많이 본다. 10G DBMS 0.1 ~ 0.4까지는 BUG로 인해 속도저하, CPU 과다소모, EXISTS/OR절 이 있는경우 속도저하등의 BUG가 있었지만 11G DBMS부터는 상당히 안정적으로 수행되는 SQL형태입니다. 개인적으로는 MERGE를 1,2번 방법보다 추천을 많이 하겠습니다. MERGE문장에 대해서는 다음번에 포스팅을 하도록 하겠습니다.
MERGE INTO customer c USING cust_src s
ON (c.customer_id = s.src_customer_id)
WHEN MATCHED THEN
UPDATE SET c.cust_address = s.cust_address
WHEN NOT MATCHED THEN
INSERT ( customer_id, cust_first_name,…)
VALUES (src_customer_id, src_first_name,…)
WHERE c.customer_id = s.src_customer_id
DELETE WHERE (c.status = 'VALID');
바. PL/SQL 루핑형 처리 3
1) 기본 개념
다량의 데이타를 Cursor로 선언후 가져와서 건건마다 Update를 수행하는 방법입니다. 보통 원쿼리형태의 SQL을 만들수 없을정도로 복잡한 업무요건이 있는 경우, 금융, 제조등 산업군에서 RULE 엔진의 의해 업무요건이 처리되는경우 이방법을 많이 사용하겠습니다. 그러나 이 방법은 원쿼리SQL형태보다는 쉽게 기술을 익힐수는 있지만 DBMS 에 가해지는 부하는 상당히 크며 장애의 원인이 될수도 있다는 것을 알아야 하겠습니다. (SQL 튜너들이 제일 싫어하는 형태가 루핑형SQL 형태입니다.) 그러나 필요하면 사용할수 있도록 방법을 익혀두길 바란다. 아래는 PL/SQL 예제중 많이 사용하는 방식입니다. 이 방식도 원쿼리로 어떻게 바꾸는지 실전예제를 통해 설명하도록 하겠습니다.
create or replace procedure emp_p(p_deptno IN emp.deptno%TYPE ) is
err_code number(10);
err_message varchar2(300);
cursor c_emp_cur is --커서선언하여 데이타를 전부 가져옴
select empno, deptno
from emp;
Begin
FOR emp_cur IN c_emp_cur --커서를 페치하며 루핑을 돌면서 데이타처리
LOOP IF emp_cur.deptno = 10 then
UPDATE EMP_t
SET SAL = sal*2
where empno = emp_cur.empno and deptno = p_deptno;
ELSIF emp_cur.deptno = 20 then
UPDATE EMP_t
SET SAL = sal*5
where empno = emp_cur.empno and deptno = p_deptno;
ELSE
UPDATE EMP_t
SET SAL = sal*10
where empno = emp_cur.empno and deptno = p_deptno;
END IF;
EXIT when c_emp_cur%NOTFOUND;
END LOOP;
exception
when others then rollback;
err_code := SQLCODE;
err_message := SQLERRM;
dbms_output.put_line(err_code ||' : '|| err_message);
end;
/
2. 실전예제
위의 PL/SQL 예제를 원쿼리형태 SQL로 변경해봅니다.
가. PL/SQL의 Dynamic SQL로 처리
--DML문을 사용하는 Dynamic SQL
create or replace procedure up_emp3(deptno number) is
begin
execute immediate
'update
(select a.sal ,case when b.deptno=10 then b.sal*2
when b.deptno=20 then b.sal*5
when b.deptno=30 then b.sal*10
else 0 end as sal_p
from emp_t a, emp b
where a.empno=b.empno
and a.deptno = ' || deptno || ' )
set sal = sal_p ';
end;
/
나. 조인 업데이트로 처리
아래와 같이 원쿼리형태의 SQL로 변경할수 있다.
가. UPDATE /*+leading(b a) use_hash(b a)*/ emp_t a
SET a.sal = 1000
where a.deptno in (select distinct deptno from emp b
where a.deptno=b.deptno and b.empno=7789);
나. UPDATE
(select /*+leading(a b) use_hash(a b)*/ a.sal ,
case when b.deptno=10 then b.sal*2
when b.deptno=20 then b.sal*5
when b.deptno=30 then b.sal*10
else 0
end as sal_p
From emp_t a, emp b
Where a.empno = b.empno and a.deptno = :deptno )
SET sal = sal_p ;
다. UPDATE emp_t a --필터처리. Set절에 in을 허용하지 않므으로 이방식은 비추천.
SET a.sal = (SELECT case when b.deptno=10 then b.sal*2
when b.deptno=20 then b.sal*5
when b.deptno=30 then b.sal*10
else 0 end as sal_p
from emp b
where b.empno = a.empno
and a.deptno = :deptno ) ;
--where절이 있다면 hash, nl 조인 가능함
다. MERGE문장으로 처리
위의 PL/SQL 루핑형SQL을 오라클 MERGE문장으로 처리한 예입니다. 위의 문장에서 IF..THEN..ELSE구조의 업무로직은 원쿼리 SQL형태에서는 DECODE, CASE절로 한번에 처리할수 있다. 현장에서 사용되는 PL/SQL 문장을 살펴보면 대부분 이 예에 해당하는것이 많다. 즉 PL/SQL문장을 원쿼리 형태롤 바꿀때 매우 중요한 개념입니다.
MERGE /*+leading(a b) use_hash(a b)*/ INTO emp_t a
USING (SELECT empno, case when deptno=10 then sal*2
when deptno=20 then sal*5
when deptno=30 then sal*10
else 0
end as sal_p
FROM emp WHERE deptno = :deptno) b
ON (a.empno = b.empno)
WHEN MATCHED THEN UPDATE SET a.sal = b.sal_p ;
3. 결언.
조인 업데이트 개념 및 처리방식은 매우 중요하며 위의 방식들은 오라클에서 사용하는것을 설명하였습니다. 그리고 요즘 새로운 방법론을 이용하여 데이타를 WAS로 가져가서 루핑을 하면서 한건씩 업데이트 처리하는 방식을 많이 사용하는 개발자들이 있는데 이 방식은 Wrong Results의 위험성을 가지고 있으며, 시스템에 과부하를 발생시키므로 추천하지 않는 방식입니다. 왜냐하면 데이타를 WAS로 가져가는데 어느정도 시간이 소요되며 그 시간에 실제 DBMS에 있는 테이블에 다른 세션에 의해 데이타 변경이 일어나면, 이미 WAS로 가져간 데이타는 잘못된 값이기 때문입니다. 이 값을 처리하게 되면 잘못된 값이 발생하게 되는 심각한 문제가 발생하게 됩니다. 즉 WAS로 데이타를 가져가서 처리하는 방법은 사용할려면 데이타를 가져가서 처리하는동안 그 해당 테이블에는 데이타가 변경되지 않도록 LOCK을 발생시켜야 하겠습니다. 그런데 테이블에 빈번한 LOCK이 발생하게 되면 다수의 사용자가 사용하는 시스템에서는 동시성이 저해되어 심각한 성능 문제가 발생하기 시작하겠습니다. 이러한 이유로 어플리케이션 개발자들도 DBMS의 LOCKING 메카니즘이나 트랜잭션의 Isolation level, 읽기 모드(Consistent Mode, Current Mode)등의 기술에 해박한 지식을 가져야 하겠습니다. 이 문제는 실전사례와 더불어 다시 포스팅하도록 하겠습니다.
결론적으로 개발자들은 업무 복잡도나 요구성능에 따라 루핑형 SQL 처리형태 뿐만 아니라,조인업데이트, MERGE 문장등 원쿼리형태 SQL로 다양하게 처리할수 있도록 해봅니다.
결론적으로 업무 복잡도에 따라 루핑형 SQL 처리형태 뿐만 아니라,조인업데이트, MERGE 문장등 원쿼리형태 SQL로 다양하게 처리할수 있도록 해봅니다.