082 데이터 입력, 수정, 삭제 한번에 하기(MERGE)

MERGE는 INSERT와 UPDATE와 DELETE를 한번에 수행하는 명령에 UPSERT로 많이 알려져있음. 

튜닝방법으로 많이 쓰임. 

---

문제 460. (복습문제) emp와 dept를 조인해서 이름과 부서위치를 출력하시오. 

SELECT e.ename, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

---

* db 모델링 중에 반정규화란? 

: 위와 같이 조인을 자주 해야하는 상황인데 조인을 할 때 너무 성능이 느리면 그냥 loc컬럼을 emp 테이블에 추가시킴.

(조인하지 않고 아래와 같이 검색해도 같은 테이블에서 조회되게 하는 것)

 

SELECT ename, loc
FROM emp;

---

문제 461. emp 테이블에 loc 컬럼을 추가하시오. (컬럼 추가 작업) 

ALTER TABLE emp
ADD loc varchar(20);

SELECT ename, loc
FROM emp;

---

문제 462. 방금 추가한 loc컬럼의 데이터를 해당 사원의 부서위치로 값을 갱신하시오. 

MERGE INTO emp E
USING dept D
ON (E.deptno = D.deptno)
WHEN MATCHED THEN
UPDATE SET E.loc = D.loc;

SELECT ename, loc
FROM emp;

---

문제 463. (복습문제) emp와 salgrade 테이블을 서로 조인해서 이름, 월급, grade를 출력하시오.

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

---

문제 464. emp 테이블에 grade 컬럼을 추가하시오.

ALTER TABLE emp
ADD (grade NUMBER(10) );

---

문제 465.  emp 테이블에 추가한 grade 컬럼에 해당사원의 월급의 등급으로 값을 갱신하시오.

MERGE INTO emp E
USING salgrade S
ON (E.sal BETWEEN S.losal AND S.hisal)
WHEN MATCHED THEN
UPDATE SET E.grade = S.grade;

SELECT ename, sal, grade
FROM emp;

조인하지 않아도 같은 테이블에서 조회되는 것을 확인할 수 있음.

 

---

문제 466. 운영 db와 테스트 db가 여러분들 컴퓨터 하나의 db에 있다고 가정하기 위해 두개의 테이블을 아래와 같이 생성하세요.

운영 db 테이블

CREATE TABLE operate_emp
AS
SELECT * FROM emp;

테스트 db 테이블 

CREATE TABLE test_emp
AS
SELECT * FROM emp;

---

문제 467. test_emp 테이블에서 직업이 SALESMAN과 MANAGER인 사원들의 데이터를 지우시오. 

 

운영 db와 테스트 db를 다르게 해서 연습해보기 위한 사전 단계 1

DELETE FROM test_emp 
WHERE JOB IN ('SALESMAN', 'MANAGER');

COMMIT;

---

문제 468. test_emp 테이블에서 직업이 ANALYST와 CLERK인 사원들의 월급을 0으로 변경하시오.

 

운영 db와 테스트 db를 다르게 해서 연습해보기 위한 사전 단계 2

UPDATE test_emp
SET sal = 0
WHERE JOB IN ('ANALYST', 'CLERK');

COMMIT;

---

문제 469. operate_emp 테이블로 test_emp 테이블을 merge하는데 operate_emp 테이블에는 존재하는 데이터인데 test_emp 테이블에 존재하지 않는 데이터는 test_emp 테이블에 입력하고, 양쪽 다 존재하는데 데이터가 다르면, 수정하시오. 

MERGE INTO test_emp T
USING operate_emp O
ON (T.empno = O.empno)
WHEN MATCHED THEN 
UPDATE SET T.sal = O.sal
WHEN NOT MATCHED THEN
INSERT (T.empno, T.ename, T.JOB, T.sal, T.comm, T.hiredate, T.mgr, T.deptno)
VALUES (O.empno, O.ename, O.JOB, O.sal, O.comm, O.hiredate, O.mgr, O.deptno);

 

구조 

코드		설명
MERGE INTO test_emp T		삽입 대상 테이블
USING operate_emp O		백업 테이블
ON (T.empno = O.empno)		연결고리
WHEN MATCHED THEN		연결고리와 매치될 때
UPDATE SET T.sal = O.sal		업데이트할 대상 나열
WHEN NOT MATCHED THEN		연결고리와 매치되지 않을 때
INSERT (T.empno, T.ename, T.JOB, T.sal,                 T.comm, T.hiredate, T.mgr, T.deptno)		삽입 대상 컬럼 나열
VALUES (O.empno, O.ename, O.JOB, O.sal,                  O.comm, O.hiredate, O.mgr, O.deptno);		삽입할 컬럼 나열

 

다소 문법이 복잡해보이지만 성능이 좋아서 MERGE문을 많이 사용
운영 db 데이터 1억건 / 테스트 db 데이터 1억건이라면 일부 데이터만 다른데 truncate하고 백업값 insert하는 것은 비효율! MERGE가 훨씬 빠른 방식