4.3.1 WITH

[with절이 없을 때]

• 상황에 따라 인라인뷰가 반복되서 성능이 안좋을 수 있다

-- 고객, 아이템유형별 주문금액 구하기 – 인라인-뷰 이용
SELECT  T0.CUS_ID ,T1.CUS_NM ,T0.ITM_TP
		,(SELECT A.BAS_CD_NM FROM C_BAS_CD A
WHERE A.LNG_CD = 'KO' AND A.BAS_CD_DV = 'ITM_TP' AND A.BAS_CD = T0.ITM_TP) ITM_TP_NM
		,T0.ORD_AMT
FROM    (
		SELECT  A.CUS_ID ,C.ITM_TP ,SUM(B.ORD_QTY * B.UNT_PRC) ORD_AMT
		FROM    T_ORD A
				,T_ORD_DET B
				,M_ITM C
		WHERE   A.ORD_SEQ = B.ORD_SEQ
		AND     B.ITM_ID = C.ITM_ID
		AND     A.ORD_DT >= TO_DATE('20170201','YYYYMMDD')
		AND     A.ORD_DT < TO_DATE('20170301','YYYYMMDD')
		GROUP BY A.CUS_ID ,C.ITM_TP
		) T0
		,M_CUS T1
WHERE   T1.CUS_ID = T0.CUS_ID
ORDER BY T0.CUS_ID ,T0.ITM_TP;

[with절로 개선한 결과]

• 성능을 개선시킨다 (항상 그런건 아님에 주의!)

-- 고객, 아이템유형별 주문금액 구하기 – WITH~AS 이용
WITH **T_CUS_ITM_AMT** AS (
	SELECT  A.CUS_ID ,C.ITM_TP ,SUM(B.ORD_QTY * B.UNT_PRC) ORD_AMT
	FROM    T_ORD A
			,T_ORD_DET B
			,M_ITM C
	WHERE   A.ORD_SEQ = B.ORD_SEQ
	AND     B.ITM_ID = C.ITM_ID
	AND     A.ORD_DT >= TO_DATE('20170201','YYYYMMDD')
	AND     A.ORD_DT < TO_DATE('20170301','YYYYMMDD')
	GROUP BY A.CUS_ID ,C.ITM_TP
) -- with가 끝날 때 콤마(;)를 쓰지 않는다
SELECT  T0.CUS_ID ,T1.CUS_NM ,T0.ITM_TP
			, (SELECT A.BAS_CD_NM FROM C_BAS_CD A
					WHERE A.LNG_CD = 'KO' AND A.BAS_CD_DV = 'ITM_TP' AND A.BAS_CD = T0.ITM_TP
			 ) ITM_TP_NM
			, T0.ORD_AMT
FROM **T_CUS_ITM_AMT** T0
		,M_CUS T1
WHERE T1.CUS_ID = T0.CUS_ID
ORDER BY T0.CUS_ID ,T0.ITM_TP;

[with절 두 번 연달아 쓸 수도 있다]

• 전/후 with절 중 후자에서 전자의 집합을 참조할 수 있다.
• 각 with절에서 동일한 테이블을 읽는 것은 성능을 악화시킨다.

-- 고객, 아이템유형별 주문금액 구하기, 전체주문 대비 주문금액비율 추가 – WITH~AS 이용
WITH **T_CUS_ITM_AMT** AS (
		SELECT  A.CUS_ID ,C.ITM_TP ,SUM(B.ORD_QTY * B.UNT_PRC) ORD_AMT
		FROM    T_ORD A
				,T_ORD_DET B
				,M_ITM C
		WHERE   A.ORD_SEQ = B.ORD_SEQ
		AND     B.ITM_ID = C.ITM_ID
		AND     A.ORD_DT >= TO_DATE('20170201','YYYYMMDD')
		AND     A.ORD_DT < TO_DATE('20170301','YYYYMMDD')
		GROUP BY A.CUS_ID ,C.ITM_TP
)
, **T_TTL_AMT** AS(
		SELECT  SUM(A.ORD_AMT) ORD_AMT
		FROM    **T_CUS_ITM_AMT** A
)
SELECT  T0.CUS_ID ,T1.CUS_NM ,T0.ITM_TP
		,(SELECT A.BAS_CD_NM FROM C_BAS_CD A
			WHERE A.LNG_CD = 'KO' AND A.BAS_CD_DV = 'ITM_TP' AND A.BAS_CD = T0.ITM_TP) ITM_TP_NM
		,T0.ORD_AMT
		,TO_CHAR(ROUND(T0.ORD_AMT / T2.ORD_AMT * 100,2)) || '%' ORD_AMT_RT 
FROM    **T_CUS_ITM_AMT** T0
		,M_CUS T1
		,**T_TTL_AMT** T2
WHERE   T1.CUS_ID = T0.CUS_ID
ORDER BY ROUND(T0.ORD_AMT / T2.ORD_AMT * 100,2) DESC;

4.3.2 WITH 절을 사용한 INSERT

• INSERT INTO 테이블명 (col1, col2, col3) WITH ~ AS () SELECT절 형식으로 INSERT가능함

-- 주문금액 비율 컬럼 추가
ALTER TABLE S_CUS_YM ADD ORD_AMT_RT NUMBER(18,3);

-- WITH~AS 절을 사용한 INSERT문
INSERT INTO S_CUS_YM (BAS_YM ,CUS_ID ,ITM_TP ,ORD_QTY ,ORD_AMT ,ORD_AMT_RT)
WITH T_CUS_ITM_AMT AS (
		SELECT  TO_CHAR(A.ORD_DT,'YYYYMM') BAS_YM ,A.CUS_ID ,C.ITM_TP
				,SUM(B.ORD_QTY) ORD_QTY ,SUM(B.ORD_QTY * B.UNT_PRC) ORD_AMT
		FROM    T_ORD A
				,T_ORD_DET B
				,M_ITM C
		WHERE   A.ORD_SEQ = B.ORD_SEQ
		AND     B.ITM_ID = C.ITM_ID
		AND     A.ORD_DT >= TO_DATE('20170401','YYYYMMDD')
		AND     A.ORD_DT < TO_DATE('20170501','YYYYMMDD')
		GROUP BY TO_CHAR(A.ORD_DT,'YYYYMM') ,A.CUS_ID ,C.ITM_TP
)
,T_TTL_AMT AS(
		SELECT  SUM(A.ORD_AMT) ORD_AMT
		FROM    T_CUS_ITM_AMT A
)
SELECT  T0.BAS_YM ,T0.CUS_ID ,T0.ITM_TP ,T0.ORD_QTY ,T0.ORD_AMT
		,ROUND(T0.ORD_AMT / T2.ORD_AMT * 100,2) ORD_AMT_RT 
FROM    T_CUS_ITM_AMT T0
		,M_CUS T1
		,T_TTL_AMT T2
WHERE   T1.CUS_ID = T0.CUS_ID;