데이터베이스 질문과 답변
by um..... [2014.04.21 16:21:22]
SELECT
RANK() OVER (ORDER BY SUM(매장판매수량) DESC) 순위,
년계.품번 "품번",
NULL "색상",
NULL "색상명",
NULL "브랜드1",
NULL "브랜드2",
NULL "년도",
NULL "시즌",
NULL "대분류",
NULL "중분류",
NULL "소분류",
NULL "부진여부",
NULL "사이즈",
NULL "제품성향",
NULL "품번명",
MAX(최초판매가) TAG가,
MAX(원가) 원가,
MAX(색상수) 색상수,
MAX(할인율) 할인율,
MAX(현판매가) 현판매가,
MAX(현판매가) 현재판매가,
MAX(부진일자) 부진일자,
TO_CHAR(TO_DATE(MIN(최초출고일),'YYYY-MM-DD'),'YYYY-MM-DD')최초출고일,
TO_DATE('20140316','YYYYMMDD')- TO_DATE(MIN(최초출고일),'YYYYMMDD')판매일수,
SUM(기획량) 기획량,
SUM(발주량) 발주량,
MIN(최초입고일) 최초입고일,
SUM(총입고수량) 총입고수량,
SUM(총입고수량*원가) 입고원가계,
SUM(총입고수량*최초판매가) 입고TAG가계,
SUM(총입고수량)-SUM(총출고수량) 창고재고수량,
SUM(총출고수량) 총출고수량,
SUM(매장판매수량) 매장판매수량,
SUM(완판판매수량) 완판판매수량,
SUM(매장판매수량)+SUM(완판판매수량) 총누계판매,
(SUM(매장판매수량)+SUM(완판판매수량)) * MAX(최초판매가) 누계최초가금액,
SUM(매장누계실판매금액) 매장누계실판매금액,
SUM(완판누계실판매금액) 완판누계실판매금액,
SUM(매장누계실판매금액)+SUM(완판누계실판매금액) 총누계실판금액,
ROUND(DECODE(SUM(총입고수량),0,0,
SUM(매장판매수량)/SUM(총입고수량)*100),1) 매장판매율,
ROUND(DECODE(SUM(총입고수량),0,0,
SUM(완판판매수량)/SUM(총입고수량)*100),1) 완판판매율,
SUM(매장기간판매) 매장기간판매,
SUM(완판기간판매) 완판기간판매,
SUM(매장기간판매)+SUM(완판기간판매) 총기간판매,
SUM(판매금액) 판매금액,
(SUM(매장기간판매)+SUM(완판기간판매)) * MAX(최초판매가) 최초가금액,
SUM(매장실판매금액) 매장실판매금액,
SUM(완판실판매금액) 완판실판매금액,
SUM(매장실판매금액)+SUM(완판실판매금액) 총기간실판금액,
TO_DATE2('20140316','YYYYMMDD') -
TO_DATE2(MIN(최초출고일),'YYYYMMDD')+1 판매기간,
MAX(색상수.생산처코드) 거래처코드,
MAX(색상수.거래처명) 거래처명
FROM 영업작지마스터 작지,
(SELECT 품번, 현판매가,할인율
FROM 영업상품판매가
WHERE (품번, 적용시작일) IN
(SELECT 품번, MAX(적용시작일) 시작일
FROM 영업상품판매가
WHERE '20140316' BETWEEN 적용시작일 AND 적용종료일
GROUP BY 품번
)
) 단가,
(SELECT 코드, 코드명 FROM 공통기초코드 WHERE 회사코드 = '0' AND 구분 = 'JAK4') 성향,
(SELECT 품번, MIN(출고일자) FROM 영업출고반품 WHERE 회사코드 = '0' GROUP BY 품번) 정상출고일,
(SELECT 품번, SUM(총발주량) FROM 영업작지마스터 WHERE 회사코드 = '0' GROUP BY 품번) 기획수량,
(SELECT 품번, COUNT(색상) 색상수, MAX(원가) 원가, MAX(생산처코드) 생산처코드, MAX(거래처명) 거래처명
FROM (SELECT DISTINCT 품번, 색상, MAX(원가합계) 원가, MAX(A.생산처코드) 생산처코드, MAX(B.거래처명) 거래처명 FROM 영업작지디테일 A, 공통거래처마스터 B
WHERE A.회사코드 = '0'
AND A.회사코드 = B.회사코드
AND A.생산처코드 = B.거래처코드
AND B.거래처구분 = 'S'
GROUP BY A.품번, A.색상)
GROUP BY 품번) 색상수,
(
SELECT
품번,
( SELECT MIN(최초출고일)
FROM 영업품번발생일
WHERE 회사코드 = '0'
AND 품번 = 년계.품번
AND 구분 IN ('C') ) 최초출고일,
( SELECT MIN(최초입고일)
FROM 영업품번발생일
WHERE 회사코드 = '0'
AND 품번 = 년계.품번
AND 구분 IN ('C') ) 최초입고일,
0 AS 기획량 ,
0 AS 발주량 ,
SUM(DECODE(누계구분,
'B', 수량,
'K',-수량,
'1', 수량,
'E', 수량,
'N',-수량,
'5',-수량
)) 총입고수량,
0 창고재고수량,
SUM(DECODE(누계구분,'L',수량,'C',-수량,
'3',수량,'7', 수량,
'6',수량,'2',-수량,0)) 총출고수량,
0 매장판매수량,
0 완판판매수량,
0 매장판매율,
0 완판판매율,
0 매장기간판매,
0 완판기간판매,
0 판매금액,
0 매장실판매금액,
0 완판실판매금액,
0 매장누계실판매금액,
0 완판누계실판매금액,
0 기간입고수량 ,
0 기간출고수량
FROM
영업창고년계 년계
WHERE 회사코드 = '0'
AND 구분제품 = '0'
AND 발생년 = 'ZZZZ'
AND 거래처코드 IN ('C0000')
AND 중분류 <> 'SA'
AND 중분류 <> 'UF'
AND SUBSTR(시즌, 1, 15) = '4' AND SUBSTR(대분류, 1, 15) = '1'
GROUP BY
품번,
NULL
UNION ALL
SELECT
품번,
'' 최초출고일,
'' 최초입고일,
0 AS 기획량 ,
0 AS 발주량 ,
0 총입고수량,
0 창고재고수량,
0 총출고수량,
SUM(DECODE(공통거래처마스터.매장판매형태,'5',0,
DECODE(누계구분,'O',수량,'E',-수량,
'S',수량,'I',-수량))) 매장판매수량,
SUM(DECODE(공통거래처마스터.매장판매형태,'5',
DECODE(누계구분,'O',수량,'E',-수량,
'S',수량,'I',-수량),0)) 완판판매수량,
0 매장판매율,
0 완판판매율,
0 매장기간판매,
0 완판기간판매,
0 판매금액,
0 매장실판매금액,
0 완판실판매금액,
SUM(DECODE(공통거래처마스터.매장판매형태,'5',0,
DECODE(누계구분,'O',실판매금액,'E',-실판매금액,
'S',실판매금액,'I',-실판매금액)))
매장누계실판매금액,
SUM(DECODE(공통거래처마스터.매장판매형태,'5',
DECODE(누계구분,'O',실판매금액,'E',-실판매금액,
'S',실판매금액,'I',-실판매금액),0))
완판누계실판매금액 ,
0 기간입고수량 ,
0 기간출고수량
FROM 공통거래처마스터,
영업매장년계 년계
WHERE 년계.회사코드 = '0'
AND 구분제품 = '0'
AND 누계구분 IN ('O','E','S','I')
AND 중분류 <> 'SA'
AND 중분류 <> 'UF'
AND SUBSTR(년계.시즌, 1, 15) = '4' AND SUBSTR(년계.대분류, 1, 15) = '1' AND 공통거래처마스터.회사코드 = 년계.회사코드
AND 공통거래처마스터.거래처코드 = 년계.매장코드
AND 공통거래처마스터.거래처구분 = 'M'
GROUP BY
품번,
NULL
UNION ALL
SELECT
품번,
'' 최초출고일,
'' 최초입고일,
0 AS 기획량 ,
0 AS 발주량 ,
0 총입고수량,
0 창고재고수량,
0 총출고수량,
0 매장판매수량,
0 완판판매수량,
0 매장판매율,
0 완판판매율,
SUM(DECODE(공통거래처마스터.매장판매형태,'5',0,
DECODE(누계구분,'O',수량,'E',-수량,
'S',수량,'I',-수량))) 매장기간판매,
SUM(DECODE(공통거래처마스터.매장판매형태,'5',
DECODE(누계구분,'O',수량,'E',-수량,
'S',수량,'I',-수량),0)) 완판기간판매,
SUM(DECODE(누계구분,'O',판매금액,'E',-판매금액,
'S',판매금액,'I',-판매금액))
판매금액,
SUM(DECODE(공통거래처마스터.매장판매형태,'5',0,
DECODE(누계구분,'O',실판매금액,'E',-실판매금액,
'S',실판매금액,'I',-실판매금액)))
매장실판매금액,
SUM(DECODE(공통거래처마스터.매장판매형태,'5',
DECODE(누계구분,'O',실판매금액,'E',-실판매금액,
'S',실판매금액,'I',-실판매금액),0))
완판실판매금액,
0 매장누계실판매금액,
0 완판누계실판매금액,
0 기간입고수량 ,
0 기간출고수량
FROM 공통거래처마스터,
영업매장일계 년계
WHERE 년계.회사코드 = '0'
AND 구분제품 = '0'
AND 누계구분 IN ('O','E','S','I')
AND 발생일자 BETWEEN '20140316' AND '20140316'
AND 중분류 <> 'SA'
AND 중분류 <> 'UF'
AND SUBSTR(년계.시즌, 1, 15) = '4' AND SUBSTR(년계.대분류, 1, 15) = '1' AND 공통거래처마스터.회사코드 = 년계.회사코드
AND 공통거래처마스터.거래처코드 = 년계.매장코드
AND 공통거래처마스터.거래처구분 = 'M'
GROUP BY
품번,
NULL
UNION ALL
SELECT
품번,
'' 최초출고일,
'' 최초입고일,
SUM(수량.기획량) AS 기획량 ,
SUM(DECODE (NVL(년계.발주량,0),0,수량.발주량, 년계.발주량)) AS 발주량 ,
0 총입고수량,
0 창고재고수량,
0 총출고수량,
0 매장판매수량,
0 완판판매수량,
0 매장판매율,
0 완판판매율,
0 매장기간판매,
0 완판기간판매,
0 판매금액,
0 매장실판매금액,
0 완판실판매금액,
0 매장누계실판매금액,
0 완판누계실판매금액,
0 기간입고수량,
0 기간출고수량
FROM
영업작지디테일 년계 ,
(SELECT 품번 STYLE , 색상 COLOR , 사이즈 SIZES ,
기획량,발주량 FROM 영업작지디테일@UBI09S_CREAS) 수량 --영업작지디테일@UBI09S_CREAS ==> UBI09S_CREAS.영업작지디테일
WHERE 년계.회사코드 = '0'
AND 중분류 <> 'SA'
AND 중분류 <> 'UF'
AND 년계.품번 = 수량.STYLE
AND 년계.색상 = 수량.COLOR
AND 년계.사이즈 = 수량.SIZES
AND SUBSTR(년계.시즌, 1, 15) = '4' AND SUBSTR(년계.대분류, 1, 15) = '1' GROUP BY
품번,
NULL
) 년계
WHERE 작지.회사코드 = '0'
AND 작지.품번 = 년계.품번
AND 작지.차수 = '01'
AND 작지.품번 = 단가.품번
AND 작지.품번 = 정상출고일.품번(+)
AND 작지.품번 = 기획수량.품번
AND 작지.품번 = 색상수.품번
AND 성향.코드 = 작지.제품성향
GROUP BY
년계.품번,
NULL
ORDER BY 순위, 품번
위의 sql의 plan을 보게 되면 다음과 같습니다.
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=117K Card=1 Bytes=361)
1 0 WINDOW (SORT) (Cost=117K Card=1 Bytes=361)
2 1 HASH (GROUP BY) (Cost=117K Card=1 Bytes=361)
3 2 HASH JOIN (Cost=117K Card=1 Bytes=361)
4 3 HASH JOIN (Cost=117K Card=1 Bytes=350)
5 4 NESTED LOOPS (Cost=116K Card=1 Bytes=286)
6 5 HASH JOIN (RIGHT SEMI) (Cost=116K Card=1 Bytes=275)
7 6 VIEW OF 'SYS.VW_NSO_1' (VIEW) (Cost=392 Card=3K Bytes=44K)
8 7 HASH (GROUP BY) (Cost=392 Card=3K Bytes=75K)
9 8 TABLE ACCESS (FULL) OF '영업상품판매가' (TABLE) (Cost=389 Card=47K Bytes=1M)
10 6 HASH JOIN (Cost=116K Card=796 Bytes=201K)
11 10 HASH JOIN (OUTER) (Cost=116K Card=182 Bytes=41K)
12 11 HASH JOIN (Cost=53K Card=182 Bytes=39K)
13 12 VIEW (Cost=53K Card=182 Bytes=34K)
14 13 UNION-ALL
15 14 HASH (GROUP BY) (Cost=2K Card=25 Bytes=975)
16 15 TABLE ACCESS (FULL) OF '영업창고년계' (TABLE) (Cost=2K Card=25 Bytes=975)
17 14 HASH (GROUP BY) (Cost=49K Card=152 Bytes=7K)
18 17 HASH JOIN (Cost=49K Card=152 Bytes=7K)
19 18 TABLE ACCESS (FULL) OF '공통거래처마스터' (TABLE) (Cost=11 Card=526 Bytes=6K)
20 18 TABLE ACCESS (FULL) OF '영업매장년계' (TABLE) (Cost=49K Card=410 Bytes=15K)
21 14 HASH (GROUP BY) (Cost=1K Card=1 Bytes=62)
22 21 NESTED LOOPS (Cost=1K Card=1 Bytes=62)
23 22 TABLE ACCESS (BY INDEX ROWID) OF '영업매장일계' (TABLE) (Cost=1K Card=1 Bytes=50)
24 23 INDEX (RANGE SCAN) OF 'PK_SAMJSUMDAILY1' (INDEX (UNIQUE)) (Cost=82 Card=3K)
25 22 TABLE ACCESS (BY INDEX ROWID) OF '공통거래처마스터' (TABLE) (Cost=1 Card=1 Bytes=12)
26 25 INDEX (UNIQUE SCAN) OF 'PK_SAGURAEMST' (INDEX (UNIQUE)) (Cost=0 Card=1)
27 14 HASH (GROUP BY) (Cost=405 Card=4 Bytes=352)
28 27 NESTED LOOPS (Cost=404 Card=4 Bytes=352)
29 28 TABLE ACCESS (FULL) OF '영업작지디테일' (TABLE) (Cost=391 Card=9 Bytes=279)
30 28 REMOTE OF '영업작지디테일@UBI09S_CREAS' (REMOTE) (Cost=2 Card=1 Bytes=57) (SERIAL_FROM_REMOTE)
31 12 TABLE ACCESS (FULL) OF '영업작지마스터' (TABLE) (Cost=43 Card=11K Bytes=325K)
32 11 VIEW (Cost=62K Card=9K Bytes=96K)
33 32 HASH (GROUP BY) (Cost=62K Card=9K Bytes=192K)
34 33 TABLE ACCESS (FULL) OF '영업출고반품' (TABLE) (Cost=61K Card=12M Bytes=245M)
35 10 TABLE ACCESS (FULL) OF '영업상품판매가' (TABLE) (Cost=389 Card=47K Bytes=1M)
36 5 INDEX (UNIQUE SCAN) OF 'PK_SACODEMST' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=11)
37 4 VIEW (Cost=862 Card=11K Bytes=666K)
38 37 HASH (GROUP BY) (Cost=862 Card=11K Bytes=572K)
39 38 VIEW (Cost=862 Card=34K Bytes=2M)
40 39 HASH (GROUP BY) (Cost=862 Card=34K Bytes=2M)
41 40 HASH JOIN (Cost=405 Card=34K Bytes=2M)
42 41 TABLE ACCESS (FULL) OF '공통거래처마스터' (TABLE) (Cost=11 Card=514 Bytes=12K)
43 41 TABLE ACCESS (FULL) OF '영업작지디테일' (TABLE) (Cost=393 Card=87K Bytes=2M)
44 3 VIEW (Cost=44 Card=11K Bytes=119K)
45 44 HASH (GROUP BY) (Cost=44 Card=11K Bytes=184K)
46 45 TABLE ACCESS (FULL) OF '영업작지마스터' (TABLE) (Cost=43 Card=11K Bytes=189K)
실행 시간은 약 40초 가량 나오고 있습니다.
위의 SQL을 보다 성능이 좋게 수정을 하고 싶습니다.. 조언을 부탁 드립니다.
INDEX정보가 있지만 너무 많은 관계로 생략하였습니다.. 감사합니다.
쭉 봤는데 참 어렸네요.
여기에 고수분들이 많기에 아마 좋은 답변 나올 수 있을거에요.
일단, 제가 advice할 점은
FULL 타는 SQL 문장들만 별도로 추출해서 실행해보세요.
일단, FULL SCAN이 속도 저하는 당연하니까요. (레코드가 많다는 전제하에)
그런 후 특정 SQL문장에 문제(속도)가 계속 되고, 개선책을 원할 경우
그 SQL 문장을 질문하시는게 훨씬 빠른 도움 받을거 같네요.
) 버튼을 클릭하여 작성 하시면 됩니다.