with를 사용하자구요... [오라클/자바/닷넷/C#/ASP.NET/아이폰/안드로이드/초보/실무/교육/학원]

union을 이용하여 데이터를 가지고 오는 경우 Query가 조건절등에서 
반복되는 경우가 있습니다. 아래의 경우에 MYBUYER라는 것은 반복되어 
나타나는 inline view인데... 
이를 with로 빼냈을때와 안빼냈을때는 속도의 많은 차이가 있습니다. 

아래의 쿼리는 백화점별 고객수와 매출수를 시간대별로 출력하는 쿼리인데 
두라인에 출력하기 위해 union을 사용하였으며 데이터 역시 서로 다른 테이블에 
있는 경우의 예입니다. 그리고 로그인한 사원의 권한이 허용되는 백화점의 매출만 
보기위해 인라인뷰로 구성했는데 이부분이 반복된것 입니다. 

더 좋은 방법이 있으시면 좀 가르쳐 주시면 좋구요...^^ 

with를 사용했을때 저의 경우는 3배이상 속도의 차이가 있었습니다.^^ 

---------------------------------------------------------------- 
전 
---------------------------------------------------------------- 
SELECT * 
FROM ( 
SELECT 
MYBUYER.BUYER_CODE CODE, 
MYBUYER.EMP_NAME||'('||MYBUYER.BUYER_CODE||')', 
'고객수' "GUBN", 
SUM(DECODE(TIME_CODE, '0930', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '1000', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '1030', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '1100', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '1130', MD_CNT,0)) "A1130", 
SUM(DECODE(TIME_CODE, '1200', MD_CNT,0)) "A1200" , 
SUM(DECODE(TIME_CODE, '1230', MD_CNT,0)) "A1230" , 
SUM(DECODE(TIME_CODE, '1300', MD_CNT,0)) "A1300" , 
SUM(DECODE(TIME_CODE, '1330', MD_CNT,0)) "A1330" , 
SUM(DECODE(TIME_CODE, '1400', MD_CNT,0)) "A1400" , 
SUM(DECODE(TIME_CODE, '1430', MD_CNT,0)) "A1430" , 
SUM(DECODE(TIME_CODE, '1500', MD_CNT,0)) "A1500" , 
SUM(DECODE(TIME_CODE, '1530', MD_CNT,0)) "A1530" , 
SUM(DECODE(TIME_CODE, '1600', MD_CNT,0)) "A1600" , 
SUM(DECODE(TIME_CODE, '1630', MD_CNT,0)) "A1630" , 
SUM(DECODE(TIME_CODE, '1700', MD_CNT,0)) "A1700" , 
SUM(DECODE(TIME_CODE, '1730', MD_CNT,0)) "A1730" , 
SUM(DECODE(TIME_CODE, '1800', MD_CNT,0)) "A1800" , 
SUM(DECODE(TIME_CODE, '1830', MD_CNT,0)) "A1830" , 
SUM(DECODE(TIME_CODE, '1900', MD_CNT,0)) "A1900" , 
SUM(DECODE(TIME_CODE, '1930', MD_CNT,0)) "A1930" , 
SUM(DECODE(TIME_CODE, '2000', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2030', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2100', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2130', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2200', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2230', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2300', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2330', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2400', MD_CNT,0)) "FINAL", 
SUM(MD_CNT) "SUM" 

FROM TANTSO T, 
( 

SELECT BUYERPC.BUYER_CODE, 
BUYERPC.EMP_NAME, 
MYPC.STORE_CODE, 
MYPC.TEAM_CODE, 
MYPC.PC_CODE 
FROM 
-- 해당 사원이 볼수 있는 모든 pc레벨 까지의 코드 
( 
SELECT DISTINCT 
A.STORE_CODE, 
A.TEAM_CODE, 
A.FLOOR_CODE, 
A.PC_CODE 
FROM TCOORG A, 
( 
SELECT STORE_CODE, 
DEPT_CODE, 
TEAM_CODE, 
FLOOR_CODE, 
PC_CODE 
FROM TCOORG, 
(SELECT JOJIK_LEVEL, 
ORG_LEVEL_VALUE 
FROM TCOPAU 
WHERE EMPNO = ?) PAU 
WHERE TCOORG.JOJIK_LEVEL LIKE DECODE(PAU.JOJIK_LEVEL,'9','%',PAU.JOJIK_LEVEL) || '%' 
AND TCOORG.ORG_LEVEL_VALUE LIKE DECODE(PAU.ORG_LEVEL_VALUE,'99','%',PAU.ORG_LEVEL_VALUE) || '%' 
AND STORE_CODE = '07' 

) B 
WHERE A.STORE_CODE = B.STORE_CODE 
AND A.DEPT_CODE = B.DEPT_CODE 
AND A.TEAM_CODE LIKE DECODE(B.TEAM_CODE ,'00','%',B.TEAM_CODE) 
AND A.FLOOR_CODE LIKE DECODE(B.FLOOR_CODE ,'00','%',B.FLOOR_CODE) 
AND A.PC_CODE LIKE DECODE(B.PC_CODE ,'00','%',B.PC_CODE) 
) MYPC, 


-- 해당 사원이 검색 가능한 모든 바이어들이 가지는 pc레벨 까지의 코드 
( 
SELECT R.BUYER_CODE BUYER_CODE, 
U.EMP_NAME EMP_NAME, 
R.STORE_CODE STORE_CODE, 
R.TEAM_CODE TEAM_CODE, 
R.PC_CODE PC_CODE 
FROM TCOBYR R, TCOBYP P, TCOUSR U 
WHERE (R.STORE_CODE, R.TEAM_CODE, R.PC_CODE) 
IN 
( 
SELECT DISTINCT 
STORE_CODE, 
TEAM_CODE, 
PC_CODE 
FROM TCOORG, 
(SELECT JOJIK_LEVEL, 
ORG_LEVEL_VALUE 
FROM TCOPAU 
WHERE EMPNO = ?) PAU 
WHERE TCOORG.JOJIK_LEVEL LIKE DECODE(PAU.JOJIK_LEVEL,'9','%',PAU.JOJIK_LEVEL) || '%' 
AND TCOORG.ORG_LEVEL_VALUE LIKE DECODE(PAU.ORG_LEVEL_VALUE,'99','%',PAU.ORG_LEVEL_VALUE) || '%' 
AND STORE_CODE = '07' 
) 
AND P.BUYER_CODE = R.BUYER_CODE 
AND P.START_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD') >
AND P.END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD') 
AND P.EMPNO = U.EMPNO 
) BUYERPC 

WHERE MYPC.STORE_CODE = BUYERPC.STORE_CODE 
AND MYPC.TEAM_CODE = BUYERPC.TEAM_CODE 
AND MYPC.PC_CODE = BUYERPC.PC_CODE 
) MYBUYER 
WHERE SALE_DATE BETWEEN ? AND ? 
AND T.STORE_CODE = DECODE(MYBUYER.STORE_CODE,'07','00',MYBUYER.STORE_CODE) 
AND T.TEAM_CODE = MYBUYER.TEAM_CODE 
AND T.PC_CODE = MYBUYER.PC_CODE 

GROUP BY MYBUYER.BUYER_CODE, MYBUYER.EMP_NAME 

UNION ALL 

SELECT 
MYBUYER.BUYER_CODE CODE, 
MYBUYER.EMP_NAME||'('||MYBUYER.BUYER_CODE||')', 
'매출액' "GUBN", 
(SUM(DECODE(TIME_CODE, '0930', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '1000', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '1030', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '1100', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '1130', SALE_AMT,0)))/1000 "A1130", 
SUM(DECODE(TIME_CODE, '1200', SALE_AMT,0))/1000 "A1200" , 
SUM(DECODE(TIME_CODE, '1230', SALE_AMT,0))/1000 "A1230" , 
SUM(DECODE(TIME_CODE, '1300', SALE_AMT,0))/1000 "A1300" , 
SUM(DECODE(TIME_CODE, '1330', SALE_AMT,0))/1000 "A1330" , 
SUM(DECODE(TIME_CODE, '1400', SALE_AMT,0))/1000 "A1400" , 
SUM(DECODE(TIME_CODE, '1430', SALE_AMT,0))/1000 "A1430" , 
SUM(DECODE(TIME_CODE, '1500', SALE_AMT,0))/1000 "A1500" , 
SUM(DECODE(TIME_CODE, '1530', SALE_AMT,0))/1000 "A1530" , 
SUM(DECODE(TIME_CODE, '1600', SALE_AMT,0))/1000 "A1600" , 
SUM(DECODE(TIME_CODE, '1630', SALE_AMT,0))/1000 "A1630" , 
SUM(DECODE(TIME_CODE, '1700', SALE_AMT,0))/1000 "A1700" , 
SUM(DECODE(TIME_CODE, '1730', SALE_AMT,0))/1000 "A1730" , 
SUM(DECODE(TIME_CODE, '1800', SALE_AMT,0))/1000 "A1800" , 
SUM(DECODE(TIME_CODE, '1830', SALE_AMT,0))/1000 "A1830" , 
SUM(DECODE(TIME_CODE, '1900', SALE_AMT,0))/1000 "A1900" , 
SUM(DECODE(TIME_CODE, '1930', SALE_AMT,0))/1000 "A1930" , 
(SUM(DECODE(TIME_CODE, '2000', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2030', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2100', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2130', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2200', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2230', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2300', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2330', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2400', SALE_AMT,0)))/1000 "FINAL", 
SUM(SALE_AMT)/1000 "SUM" 

FROM TANTSO T, 
( 
SELECT BUYERPC.BUYER_CODE, 
BUYERPC.EMP_NAME, 
MYPC.STORE_CODE, 
MYPC.TEAM_CODE, 
MYPC.FLOOR_CODE, 
MYPC.PC_CODE 
FROM 
-- 해당 사원이 볼수 있는 모든 pc레벨 까지의 코드 
( 
SELECT DISTINCT 
A.STORE_CODE, 
A.TEAM_CODE, 
A.FLOOR_CODE, 
A.PC_CODE 
FROM TCOORG A, 
( 
SELECT STORE_CODE, 
DEPT_CODE, 
TEAM_CODE, 
FLOOR_CODE, 
PC_CODE 
FROM TCOORG, 
(SELECT JOJIK_LEVEL, 
ORG_LEVEL_VALUE 
FROM TCOPAU 
WHERE EMPNO = ?) PAU 
WHERE TCOORG.JOJIK_LEVEL LIKE DECODE(PAU.JOJIK_LEVEL,'9','%',PAU.JOJIK_LEVEL) || '%' 
AND TCOORG.ORG_LEVEL_VALUE LIKE DECODE(PAU.ORG_LEVEL_VALUE,'99','%',PAU.ORG_LEVEL_VALUE) || '%' 
AND STORE_CODE = '07' 

) B 
WHERE A.STORE_CODE = B.STORE_CODE 
AND A.DEPT_CODE = B.DEPT_CODE 
AND A.TEAM_CODE LIKE DECODE(B.TEAM_CODE ,'00','%',B.TEAM_CODE) 
AND A.FLOOR_CODE LIKE DECODE(B.FLOOR_CODE ,'00','%',B.FLOOR_CODE) 
AND A.PC_CODE LIKE DECODE(B.PC_CODE ,'00','%',B.PC_CODE) 
) MYPC, 


-- 해당 사원이 검색 가능한 모든 바이어들이 가지는 pc레벨 까지의 코드 
( 
SELECT R.BUYER_CODE BUYER_CODE, 
U.EMP_NAME EMP_NAME, 
R.STORE_CODE STORE_CODE, 
R.TEAM_CODE TEAM_CODE, 
R.PC_CODE PC_CODE 
FROM TCOBYR R, TCOBYP P, TCOUSR U 
WHERE (R.STORE_CODE, R.TEAM_CODE, R.PC_CODE) 
IN 
( 
SELECT DISTINCT 
STORE_CODE, 
TEAM_CODE, 
PC_CODE 
FROM TCOORG, 
(SELECT JOJIK_LEVEL, 
ORG_LEVEL_VALUE 
FROM TCOPAU 
WHERE EMPNO = ?) PAU 
WHERE TCOORG.JOJIK_LEVEL LIKE DECODE(PAU.JOJIK_LEVEL,'9','%',PAU.JOJIK_LEVEL) || '%' 
AND TCOORG.ORG_LEVEL_VALUE LIKE DECODE(PAU.ORG_LEVEL_VALUE,'99','%',PAU.ORG_LEVEL_VALUE) || '%' 
AND STORE_CODE = '07' 
) 
AND P.BUYER_CODE = R.BUYER_CODE 
AND P.START_DATE <= TO_CHAR(SYSDATE, 'YYYYMMDD') >
AND P.END_DATE >= TO_CHAR(SYSDATE, 'YYYYMMDD') 
AND P.EMPNO = U.EMPNO 
) BUYERPC 

WHERE MYPC.STORE_CODE = BUYERPC.STORE_CODE 
AND MYPC.TEAM_CODE = BUYERPC.TEAM_CODE 
AND MYPC.PC_CODE = BUYERPC.PC_CODE 
) MYBUYER 

WHERE SALE_DATE BETWEEN ? AND ? 
AND T.STORE_CODE = DECODE(MYBUYER.STORE_CODE,'07','00',MYBUYER.STORE_CODE) 
AND T.TEAM_CODE = MYBUYER.TEAM_CODE 
AND T.PC_CODE = MYBUYER.PC_CODE 

GROUP BY MYBUYER.BUYER_CODE, MYBUYER.EMP_NAME 
) 
ORDER BY 1, 2 



------------------------------------------------------- 
후 
------------------------------------------------------- 

WITH MYBUYER AS ( 
..... (위에서 빨간색으로 되어 있는 부분) 
) 

SELECT * 
FROM ( 
SELECT 
MYBUYER.BUYER_CODE CODE, 
MYBUYER.EMP_NAME||'('||MYBUYER.BUYER_CODE||')', 
'???' "GUBN", 
SUM(DECODE(TIME_CODE, '0930', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '1000', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '1030', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '1100', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '1130', MD_CNT,0)) "A1130", 
SUM(DECODE(TIME_CODE, '1200', MD_CNT,0)) "A1200" , 
SUM(DECODE(TIME_CODE, '1230', MD_CNT,0)) "A1230" , 
SUM(DECODE(TIME_CODE, '1300', MD_CNT,0)) "A1300" , 
SUM(DECODE(TIME_CODE, '1330', MD_CNT,0)) "A1330" , 
SUM(DECODE(TIME_CODE, '1400', MD_CNT,0)) "A1400" , 
SUM(DECODE(TIME_CODE, '1430', MD_CNT,0)) "A1430" , 
SUM(DECODE(TIME_CODE, '1500', MD_CNT,0)) "A1500" , 
SUM(DECODE(TIME_CODE, '1530', MD_CNT,0)) "A1530" , 
SUM(DECODE(TIME_CODE, '1600', MD_CNT,0)) "A1600" , 
SUM(DECODE(TIME_CODE, '1630', MD_CNT,0)) "A1630" , 
SUM(DECODE(TIME_CODE, '1700', MD_CNT,0)) "A1700" , 
SUM(DECODE(TIME_CODE, '1730', MD_CNT,0)) "A1730" , 
SUM(DECODE(TIME_CODE, '1800', MD_CNT,0)) "A1800" , 
SUM(DECODE(TIME_CODE, '1830', MD_CNT,0)) "A1830" , 
SUM(DECODE(TIME_CODE, '1900', MD_CNT,0)) "A1900" , 
SUM(DECODE(TIME_CODE, '1930', MD_CNT,0)) "A1930" , 
SUM(DECODE(TIME_CODE, '2000', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2030', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2100', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2130', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2200', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2230', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2300', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2330', MD_CNT,0))+ 
SUM(DECODE(TIME_CODE, '2400', MD_CNT,0)) "FINAL", 
SUM(MD_CNT) "SUM" 

FROM TANTSO T, 
( 
SELECT * FROM MYBUYER 
) MYBUYER 
WHERE SALE_DATE BETWEEN ? AND ? 
AND T.STORE_CODE = DECODE(MYBUYER.STORE_CODE,'07','00',MYBUYER.STORE_CODE) 
AND T.TEAM_CODE = MYBUYER.TEAM_CODE 
AND T.PC_CODE = MYBUYER.PC_CODE 

GROUP BY MYBUYER.BUYER_CODE, MYBUYER.EMP_NAME 

UNION ALL 

SELECT 
MYBUYER.BUYER_CODE CODE, 
MYBUYER.EMP_NAME||'('||MYBUYER.BUYER_CODE||')', 
'???' "GUBN", 
(SUM(DECODE(TIME_CODE, '0930', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '1000', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '1030', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '1100', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '1130', SALE_AMT,0)))/1000 "A1130", 
SUM(DECODE(TIME_CODE, '1200', SALE_AMT,0))/1000 "A1200" , 
SUM(DECODE(TIME_CODE, '1230', SALE_AMT,0))/1000 "A1230" , 
SUM(DECODE(TIME_CODE, '1300', SALE_AMT,0))/1000 "A1300" , 
SUM(DECODE(TIME_CODE, '1330', SALE_AMT,0))/1000 "A1330" , 
SUM(DECODE(TIME_CODE, '1400', SALE_AMT,0))/1000 "A1400" , 
SUM(DECODE(TIME_CODE, '1430', SALE_AMT,0))/1000 "A1430" , 
SUM(DECODE(TIME_CODE, '1500', SALE_AMT,0))/1000 "A1500" , 
SUM(DECODE(TIME_CODE, '1530', SALE_AMT,0))/1000 "A1530" , 
SUM(DECODE(TIME_CODE, '1600', SALE_AMT,0))/1000 "A1600" , 
SUM(DECODE(TIME_CODE, '1630', SALE_AMT,0))/1000 "A1630" , 
SUM(DECODE(TIME_CODE, '1700', SALE_AMT,0))/1000 "A1700" , 
SUM(DECODE(TIME_CODE, '1730', SALE_AMT,0))/1000 "A1730" , 
SUM(DECODE(TIME_CODE, '1800', SALE_AMT,0))/1000 "A1800" , 
SUM(DECODE(TIME_CODE, '1830', SALE_AMT,0))/1000 "A1830" , 
SUM(DECODE(TIME_CODE, '1900', SALE_AMT,0))/1000 "A1900" , 
SUM(DECODE(TIME_CODE, '1930', SALE_AMT,0))/1000 "A1930" , 
(SUM(DECODE(TIME_CODE, '2000', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2030', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2100', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2130', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2200', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2230', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2300', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2330', SALE_AMT,0))+ 
SUM(DECODE(TIME_CODE, '2400', SALE_AMT,0)))/1000 "FINAL", 
SUM(SALE_AMT)/1000 "SUM" 

FROM TANTSO T, 
( 
SELECT * FROM MYBUYER 
) MYBUYER 

WHERE SALE_DATE BETWEEN ? AND ? 
AND T.STORE_CODE = DECODE(MYBUYER.STORE_CODE,'07','00',MYBUYER.STORE_CODE) 
AND T.TEAM_CODE = MYBUYER.TEAM_CODE 
AND T.PC_CODE = MYBUYER.PC_CODE 

GROUP BY MYBUYER.BUYER_CODE, MYBUYER.EMP_NAME 
) 
ORDER BY 1, 2

[출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=oracleTip&wr_id=560

• JAVA
• ORACLE
• iPhone/Android
• .NET
• 표준웹/HTML5
• 채용/취업무료교육
• 초보자(재학생)코스