Oracle WITH절 기본 사용법 및 응용 2
Updated:
예제 테이블 생성
샘플 테이블 생성
오라클 11G DBMS를 설치하면 샘플 유저인 SCOTT 유저가 설치되는데 EMP 와 DEPT라는 테이블을 기준으로 테스트해보겠습니다. DEPT 테이블의 부서별 위치를 가진 테이블이고 EMP 테이블의 부모테이블로 DEPTNO가 PK입니다. EMP는 고용인 테이블로서 DEPT의 자식테이블로 EMPNO가 PK이며, DEPTNO가 FK입니다. 아래의 예제를 가지고 알아봅니다.
- 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 |
- DEPT테이블
| DEPTNO | DNAME | LOC |
|---|---|---|
| 10 | ACCOUNTING} | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
1. 오라클 WITH 절
1편에 이어 2편에서는 오라클 WITH절과 UNION ALL, DECODE/CASE절과의 상호 변경하는 방법을 알아봅니다.
UNION ALL을 사용하여 동일 테이블에 접근하는 SQL형태는 WITH절과 DECODE/CASE로 서로 변경할수 있습니다. 변경의 장점은 UNION ALL을 WITH절로 변경시 UNION ALL에서 반복 접근하는 테이블이 있는경우 WITH절로 변경하면 반복적인 디스크IO(테이블접근)을 메모리 접근이 가능하도록 Materilize 방식을 사용하여 성능을 개선할수 있습니다. UNION ALL을 DECODE/CASE로 변경 가능한경우는 DECODE/CASE절은 대부분 한번의 테이블 접근만 하면 해결되므로 굉장한 성능향상을 이룰수 있습니다. 실전 예제를 통해 알아봅니다.
2. SQL변경 실전예제
가. UNION ALL을 WITH로 변경
변졍전 UNION ALL문장을 살펴보면 동일 테이블(TB_KPA023)에 반복 접근하는 인라인뷰들이 있고 UNION ALL로 연결되어 있습니다. 이런 구조는 최소한 WITH절로 변경할수 있고 운이 좋아 DECODE/CASE로 변경할수 있습니다면 테이블 접근을 한번만 하고 원하는 값을 처리할수 있습니다. 그러나 지금 예제는 DRCODE/CASE로 변환할수 없는 예제라 WITH절까지만 변환합니다.
WITH절로 변환할때에는 WITH절 정리 1편의 예와 같이 몇가지 점검을 하여야 합니다. 많이 사용하는 화면인지 여부, WITH절안의 SQL이 레코드건수를 얼마나 처리하여 공유메모리에 임시테이블을 만드는지 확인하고(sql 빈번도에 따라 다르지만 최대 몇백에서 몇만건수준 권장함), 이 SQL이 초당 몇번정도 사용하는 SQL인지도 점검하여 WITH절 전환여부를 판단하여야 합니다. 다행스럽게 빈번하게 사용하지 않는 SQL이었고 DB버젼도 10G의 안전한 버젼이었으며, WITH절의 처리 데이타량이 몇천건 수준이어서 WITH절 변경을 하게되었습니다.
변경전 SQL에서 UNION ALL로 연결된 SQL들이 매우 비슷한데 인라인뷰 안에서 동일 테이블을 반복접근하고 있고 WHERE조건만 약간 다르게 하여 처리하고 있습니다. WHERE 조건중 ‘=’로 비교되고 인덱스를 걸만한 칼럼이 있는 조건을 찾아보니 JRDC_BROF_CD LIKE :jrdc_brof_cd 조건이 요구사항에 맞아서 WITH절에 넣어주고 인덱스를 생성하여 주었다. /+MATERILIZE/ 힌트를 주고 테스트해보니 1분정도 소요되는 SQL이 1초이내로 속도개선이 되었습니다.
가. 변경전
SELECT *
FROM
-- 주소변경자 new 발생 --
(SELECT COUNT(*) CNT
FROM TB_KPA023
WHERE PRCS_YM = :prcs_ym
AND FOF_DATA_DVCD = '1'
AND JRDC_BROF_CD LIKE :jrdc_brof_cd) A,
-- 주소변경자 new 처리
(SELECT COUNT(*) CNT
FROM TB_KPA023
WHERE PRCS_YM = :prcs_ym
AND FOF_DATA_DVCD = '1'
AND JRDC_BROF_CD LIKE :jrdc_brof_cd
AND FOF_CNFM_DT <> '00010101') B,
-- 주소변경자 new 미처리
(SELECT COUNT(*) CNT
FROM TB_KPA023
WHERE PRCS_YM = :prcs_ym
AND FOF_DATA_DVCD = '1'
AND JRDC_BROF_CD LIKE :jrdc_brof_cd
AND FOF_CNFM_DT = '00010101') C,
-- 주소변경자 old 발생
(SELECT COUNT(*) CNT
FROM TB_KPA023
WHERE PRCS_YM < :prcs_ym
AND FOF_DATA_DVCD = '1'
AND JRDC_BROF_CD LIKE :jrdc_brof_cd) D,
……
나. 변경후
WITH
TB_KPA0231 AS ( SELECT /*+MATERILIZE*/ *
FROM TB_KPA023
WHERE
JRDC_BROF_CD LIKE :jrdc_brof_cd )
SELECT *
FROM
-- 주소변경자 new 발생
(SELECT COUNT(*) CNT
FROM TB_KPA0231
WHERE PRCS_YM = :prcs_ym
AND FOF_DATA_DVCD = '1' ) A,
-- 주소변경자 new 처리
(SELECT COUNT(*) CNT
FROM TB_KPA0231
WHERE PRCS_YM = :prcs_ym
AND FOF_DATA_DVCD = '1'
AND FOF_CNFM_DT <> '00010101') B,
-- 주소변경자 new 미처리
(SELECT COUNT(*) CNT
FROM TB_KPA0231
WHERE PRCS_YM = :prcs_ym
AND FOF_DATA_DVCD= '1'
AND FOF_CNFM_DT = '00010101') C,
.....
나. UNION ALL을 WITH로 변경 2
가항과 비슷한 개념으로 UNION ALL을 WITH로 변경처리 하였습니다.
- 예제 1. EMP와 DEPT테이블에서 부서번호와 위치, 그리고 부서번호별 연봉을 구하시오.
DEPTNO LOC SUM(SAL)
---------------------------
10 NEW YORK 1300
20 DALLAS 2975
30 CHICAGO 5600
가. 변경전
SELECT a.deptno, b.loc, sum(a.sal)
FROM emp a, dept b
WHERE a.deptno = b.deptno
and a.job='CLERK'
and a.deptno=10
GROUP BY a.deptno, b.loc
Union ALL
SELECT a.deptno, b.loc, sum(a.sal)
FROM emp a, dept b
WHERE a.deptno = b.deptno
and a.job='MANAGER'
and a.deptno=20
GROUP BY a.deptno, b.loc
Union ALL
SELECT a.deptno, b.loc, sum(a.sal)
FROM emp a, dept b
WHERE a.deptno = b.deptno
and a.job='SALESMAN'
and a.deptno=30
GROUP BY a.deptno, b.loc;
--인라인뷰 dc를 with절로 선언하여 처리합니다.
나. 변경후
WITH AA AS
(SELECT a.deptno, a.job, b.loc, a.sal
FROM emp a, dept b
WHERE a.deptno = b.deptno)
SELECT deptno, loc, sum(sal)
FROM aa
WHERE job='CLERK'
and deptno=10
GROUP BY deptno, loc
Union ALL
SELECT deptno, loc, sum(sal)
FROM aa
WHERE job='MANAGER'
and deptno=20
GROUP BY deptno, loc
Union ALL
SELECT deptno, loc, sum(sal)
FROM aa
WHERE job='SALESMAN'
and deptno=30
GROUP BY deptno, loc ;
다. UNION ALL을 DECODE/CASE로 변경
이번 예제에서는 변졍전 UNION ALL문장을 살펴보면 SQL구문이 똑같은데 단지 변수값만 살짝 수정해서 UNION ALL로 묶여져 있습니다. 이런 SQL형태는 DECODE/CASE문장으로 변경가능하다. 이때 DECODE/CASE는 반복 접근하는 테이블을 단 한번만 접근하고 처리 가능하다. 그래서 UNION ALL구문보다 월등한 성능개선을 이룰수있습니다. 이런 형태의 SQL이 많으니 변경하는것을 암기하는것도 좋을것 같다.
가. 변경전
SELECT TRTM_TYPE -- 1
,COUNT(*)
FROM TB_BPMZNTOI
WHERE TRTM_TYPE = 'PYM'
GROUP BY TRTM_TYPE
UNION ALL
SELECT TRTM_TYPE -- 2
,COUNT(*)
FROM TB_BPMZNTOI
WHERE TRTM_TYPE = 'BCK'
GROUP BY TRTM_TYPE
UNION ALL
SELECT TRTM_TYPE -- 3
,COUNT(*)
FROM TB_BPMZNTOI
WHERE TRTM_TYPE = 'ADJ'
GROUP BY TRTM_TYPE
UNION ALL
SELECT TRTM_TYPE -- 4
,COUNT(*)
FROM TB_BPMZNTOI
WHERE TRTM_TYPE = 'ADJR'
GROUP BY TRTM_TYPE
UNION ALL
SELECT TRTM_TYPE -- 5
,COUNT(*)
FROM TB_BPMZNTOI
WHERE TRTM_TYPE = 'RFN'
GROUP BY TRTM_TYPE
UNION ALL
SELECT TRTM_TYPE -- 6
,COUNT(*)
FROM TB_BPMZNTOI
WHERE TRTM_TYPE = 'RFN'
GROUP BY TRTM_TYPE
UNION ALL
SELECT TRTM_TYPE -- 청구
,COUNT(*)
FROM TB_BPMZNTOI
WHERE TRTM_TYPE = 'INV'
GROUP BY TRTM_TYPE
.........
나. 변경후
SELECT CASE WHEN TRTM_TYPE = 'PYM' THEN '1.'
WHEN TRTM_TYPE = 'BCK' THEN '2.'
WHEN TRTM_TYPE = 'ADJ' THEN '3.'
WHEN TRTM_TYPE = 'ADJR' THEN '4.'
WHEN TRTM_TYPE = 'RFN' THEN '5.'
WHEN TRTM_TYPE = 'RFNR' THEN '6.'
WHEN TRTM_TYPE = 'INV' THEN '7.'
ELSE 'a.기타' END GB,
COUNT(0) JUBSU,
SUM(CASE WHEN ICIS_ACPT_STAT = '02' THEN 1 END) CHORI
FROM TB_BPMZNTOI
WHERE TO_CHAR(GEAR_DATE,'YYYYMMDD') ='20100914'
GROUP BY CASE WHEN TRTM_TYPE = 'PYM' THEN '1.'
WHEN TRTM_TYPE = 'BCK' THEN '2.'
WHEN TRTM_TYPE = 'ADJ' THEN '3.'
WHEN TRTM_TYPE = 'ADJR' THEN '4.'
WHEN TRTM_TYPE = 'RFN' THEN '5.'
WHEN TRTM_TYPE = 'RFNR' THEN '6.'
WHEN TRTM_TYPE = 'INV' THEN '7.'
ELSE 'a.기타'
END
ORDER BY 1;
라. UNION ALL을 DECODE/CASE로 변경 2
아래의 예제는 20년전 저술된 ‘오라클 튜닝실무’ 책에서 가져온 예입니다. 역시 다항과 비슷한 개념인데 인라인뷰를 보면 여러번 반복 사용하는 SQL이 보이고 WHERE조건의 상수값만 수정되어 사용되고 있습니다. 이런 문장은 대부분 DECODE/CASE절로 한번만 접근하여 처리할수 있습니다.
가. 변경전
INSERT INTO 월별요금납부실적 ( 고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)
SELECT K.고객번호, '200903' 납입월 , A.납입금액 지로 , B.납입금액 자동이체 , C.납입금액 신용카드 ,
D.납입금액 핸드폰 , E.납입금액 인터넷
FROM 고객 K ,
(SELECT 고객번호, 납입금액 FROM 월별납입방법별집계 WHERE 납입월 = '200903' AND 납입방법코드 = 'A') A ,
(SELECT 고객번호, 납입금액 FROM 월별납입방법별집계 WHERE 납입월 = '200903' AND 납입방법코드 = 'B') B ,
(SELECT 고객번호, 납입금액 FROM 월별납입방법별집계 WHERE 납입월 = '200903' AND 납입방법코드 = 'C') C ,
(SELECT 고객번호, 납입금액 FROM 월별납입방법별집계 WHERE 납입월 = '200903' AND 납입방법코드 = 'D') D ,
(SELECT 고객번호, 납입금액 FROM 월별납입방법별집계 WHERE 납입월 = '200903' AND 납입방법코드 = 'E') E
WHERE A.고객번호(+) = K.고객번호
AND B.고객번호(+) = K.고객번호
AND C.고객번호(+) = K.고객번호
AND D.고객번호(+) = K.고객번호
AND E.고객번호(+) = K.고객번호
AND NVL(A.납입금액, 0)+NVL(B.납입금액, 0)+ NVL(C.납입금액, 0)+NVL(D.납입금액, 0)+NVL(E.납입금액, 0) > 0
나. 변경후
INSERT INTO 월별요금납부실적 (고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)
SELECT 고객번호, 납입월 ,
NVL(SUM(CASE WHEN 납입방법코드 = 'A' THEN 납입금액 END), 0) 지로 ,
NVL(SUM(CASE WHEN 납입방법코드 = 'B' THEN 납입금액 END), 0) 자동이체 ,
NVL(SUM(CASE WHEN 납입방법코드 = 'C' THEN 납입금액 END), 0) 신용카드 ,
NVL(SUM(CASE WHEN 납입방법코드 = 'D' THEN 납입금액 END), 0) 핸드폰 ,
NVL(SUM(CASE WHEN 납입방법코드 = 'E' TJEM 납입금액 END), 0) 인터넷
FROM 월별납입방법별집계
WHERE 납입월 = '200903'
GROUP BY 고객번호, 납입월 ;
3. 결언
오라클 WITH는 매우 중요한 기능입니다. 이번 칼럼에서는 UNION ALL로 만들어진 반복접근테이블 많은 SQL을 WITH나 DECODE/CASE절로 변경하는 방법을 알아 보았다. 이 방법은 대부분 DBMS에 사용할수 있는 방법으로 SQL튜너들이 많이 사용하는 기술중 하나입니다.