[Oracle] 오라클 SQL 튜닝, 집계함수, 누적 구하기, JOIN, GROUP BY
2012년 1월에 팔린 상품의 일별 상품별 판매수량과 판매금액 및 각각의 랭킹 구하기
* 결과값
：
：
：
* 쿼리1
SELECT O.ORD_DT "판매일자",
I.ITEM_ID "상품ID",
I.ITEM_NM "상품명",
SUM(O.ORD_ITEM_QTY) "판매수량",
SUM(O.ORD_ITEM_QTY*P.PRICE) "판매금액",
RANK() OVER(PARTITION BY O.ORD_DT ORDER BY SUM(O.ORD_ITEM_QTY) DESC) "판매수량랭킹",
RANK() OVER(PARTITION BY O.ORD_DT ORDER BY SUM(O.ORD_ITEM_QTY*P.PRICE) DESC)
"판매금액랭킹"
FROM ORD_ITEM O
LEFT OUTER JOIN ITEM I
ON O.ITEM_ID = I.ITEM_ID,
(
SELECT P.ITEM_ID,
SUM(P.SALE_PRICE) PRICE
FROM UITEM_PRICE P
GROUP BY P.ITEM_ID
) P
WHERE I.ITEM_ID = P.ITEM_ID
AND O.ORD_DT BETWEEN '20120101' AND '20120131'
GROUP BY O.ORD_DT, I.ITEM_ID, I.ITEM_NM
ORDER BY O.ORD_DT, I.ITEM_ID;
* 실행계획
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2852 |00:00:01.78 | 43431 |
| 1 | SORT ORDER BY | | 1 | 501 | 2852 |00:00:01.78 | 43431 |
| 2 | WINDOW SORT | | 1 | 501 | 2852 |00:00:01.78 | 43431 |
| 3 | WINDOW SORT | | 1 | 501 | 2852 |00:00:01.77 | 43431 |
| 4 | HASH GROUP BY | | 1 | 501 | 2852 |00:00:01.77 | 43431 |
|* 5 | HASH JOIN | | 1 | 535K| 1546K|00:00:01.28 | 43431 |
| 6 | NESTED LOOPS | | 1 | 92 | 92 |00:00:00.01 | 12 |
| 7 | TABLE ACCESS FULL | ITEM | 1 | 92 | 92 |00:00:00.01 | 7 |
| 8 | VIEW PUSHED PREDICATE | | 92 | 1 | 92 |00:00:00.01 | 5 |
|* 9 | FILTER | | 92 | | 92 |00:00:00.01 | 5 |
| 10 | SORT AGGREGATE | | 92 | 1 | 92 |00:00:00.01 | 5 |
| 11 | TABLE ACCESS BY INDEX ROWID| UITEM_PRICE | 92 | 1 | 130 |00:00:00.01 | 5 |
|* 12 | INDEX RANGE SCAN | UITEM_PRICE_PK | 92 | 1 | 130 |00:00:00.01 | 3 |
| 13 | TABLE ACCESS BY INDEX ROWID | ORD_ITEM | 1 | 535K| 1546K|00:00:00.73 | 43419 |
|* 14 | INDEX RANGE SCAN | ORD_ITEM_X01 | 1 | 535K| 1546K|00:00:00.20 | 5815 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("O"."ITEM_ID"="I"."ITEM_ID")
9 - filter((COUNT(*)>0 AND '20120101'<='20120131'))
12 - access("P"."ITEM_ID"="I"."ITEM_ID")
14 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')
※ JOIN 전에 상품+주문 테이블을 GROUP BY 해주는 게 더 효율적
* 쿼리2
SELECT O.ORD_DT "판매일자",
O.ITEM_ID "상품ID",
O.ITEM_NM "상품명",
O.QTY "판매수량",
P.PRICE*O.QTY "판매금액",
RANK() OVER(PARTITION BY ORD_DT ORDER BY O.QTY DESC) "판매수량랭킹",
RANK() OVER(PARTITION BY ORD_DT ORDER BY P.PRICE*O.QTY DESC) "판매금액랭킹"
FROM
(
SELECT O.ORD_DT,
I.ITEM_ID,
I.ITEM_NM,
SUM(O.ORD_ITEM_QTY) QTY
FROM ORD_ITEM O
LEFT OUTER JOIN ITEM I
ON O.ITEM_ID = I.ITEM_ID
WHERE O.ORD_DT BETWEEN '20120101' AND '20120131'
GROUP BY O.ORD_DT, I.ITEM_ID, I.ITEM_NM
) O,
(
SELECT P.ITEM_ID,
SUM(P.SALE_PRICE) PRICE
FROM UITEM_PRICE P
GROUP BY P.ITEM_ID
) P
WHERE O.ITEM_ID = P.ITEM_ID
ORDER BY O.ORD_DT, O.ITEM_ID;
* 실행계획
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2852 |00:00:01.70 | 43429 |
| 1 | SORT ORDER BY | | 1 | 46047 | 2852 |00:00:01.70 | 43429 |
| 2 | WINDOW SORT | | 1 | 46047 | 2852 |00:00:01.69 | 43429 |
| 3 | WINDOW SORT | | 1 | 46047 | 2852 |00:00:01.69 | 43429 |
|* 4 | HASH JOIN | | 1 | 46047 | 2852 |00:00:01.69 | 43429 |
| 5 | VIEW | | 1 | 92 | 92 |00:00:00.01 | 3 |
| 6 | HASH GROUP BY | | 1 | 92 | 92 |00:00:00.01 | 3 |
| 7 | TABLE ACCESS BY INDEX ROWID | UITEM_PRICE | 1 | 130 | 130 |00:00:00.01 | 3 |
| 8 | INDEX FULL SCAN | UITEM_PRICE_PK | 1 | 130 | 130 |00:00:00.01 | 1 |
| 9 | VIEW | | 1 | 46047 | 2852 |00:00:01.68 | 43426 |
| 10 | HASH GROUP BY | | 1 | 46047 | 2852 |00:00:01.68 | 43426 |
|* 11 | HASH JOIN RIGHT OUTER | | 1 | 535K| 1546K|00:00:01.25 | 43426 |
| 12 | TABLE ACCESS FULL | ITEM | 1 | 92 | 92 |00:00:00.01 | 7 |
| 13 | TABLE ACCESS BY INDEX ROWID| ORD_ITEM | 1 | 535K| 1546K|00:00:00.73 | 43419 |
|* 14 | INDEX RANGE SCAN | ORD_ITEM_X01 | 1 | 535K| 1546K|00:00:00.20 | 5815 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("O"."ITEM_ID"="P"."ITEM_ID")
11 - access("O"."ITEM_ID"="I"."ITEM_ID")
14 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')
☞ 그닥 극적인 효과는 없지만 조회하는 ORD_DT 범위가 넓어질수록 효과가 나타남
2012년 1월에 팔린 상품의 일별 매장별 판매수량과 판매금액 및 누적 판매금액 구하기
* 결과값
* 쿼리
SELECT O.ORD_DT "판매일시",
MAX(O.BRANCH_CD) "매장코드",
SUM(I.ORD_ITEM_QTY) "판매수량",
SUM(I.ORD_ITEM_QTY*P.PRICE) "판매금액",
SUM(SUM(I.ORD_ITEM_QTY*P.PRICE)) OVER(ORDER BY O.ORD_DT
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "누적"
FROM ORD O,
ORD_ITEM I,
(
SELECT P.ITEM_ID,
SUM(P.SALE_PRICE) PRICE
FROM UITEM_PRICE P
GROUP BY P.ITEM_ID
) P
WHERE I.ITEM_ID = P.ITEM_ID
AND O.ORD_NO = I.ORD_NO
AND I.ITEM_ID = P.ITEM_ID
AND O.ORD_DT BETWEEN '20120101' AND '20120131'
AND O.BRANCH_CD = :BRANCH_CD
GROUP BY O.ORD_DT, O.BRANCH_CD
ORDER BY O.ORD_DT;
* 실행계획
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 31 |00:00:00.33 | 25978 |
| 1 | WINDOW BUFFER | | 1 | 3 | 31 |00:00:00.33 | 25978 |
| 2 | SORT GROUP BY | | 1 | 3 | 31 |00:00:00.33 | 25978 |
|* 3 | HASH JOIN | | 1 | 749 | 7683 |00:00:00.32 | 25978 |
| 4 | VIEW | | 1 | 92 | 92 |00:00:00.01 | 3 |
| 5 | HASH GROUP BY | | 1 | 92 | 92 |00:00:00.01 | 3 |
| 6 | TABLE ACCESS BY INDEX ROWID| UITEM_PRICE | 1 | 130 | 130 |00:00:00.01 | 3 |
| 7 | INDEX FULL SCAN | UITEM_PRICE_PK | 1 | 130 | 130 |00:00:00.01 | 1 |
| 8 | NESTED LOOPS | | 1 | | 7683 |00:00:00.32 | 25975 |
| 9 | NESTED LOOPS | | 1 | 749 | 7683 |00:00:00.31 | 22873 |
|* 10 | TABLE ACCESS BY INDEX ROWID| ORD | 1 | 305 | 3134 |00:00:00.29 | 16579 |
|* 11 | INDEX RANGE SCAN | ORD_X01 | 1 | 62331 | 625K|00:00:00.08 | 2354 |
|* 12 | INDEX RANGE SCAN | ORD_ITEM_PK | 3134 | 2 | 7683 |00:00:00.01 | 6294 |
| 13 | TABLE ACCESS BY INDEX ROWID | ORD_ITEM | 7683 | 2 | 7683 |00:00:00.01 | 3102 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("I"."ITEM_ID"="P"."ITEM_ID")
10 - filter("O"."BRANCH_CD"=:BRANCH_CD)
11 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')
12 - access("O"."ORD_NO"="I"."ORD_NO")
※ Index에서 Table Random Access하는 과정(11→10)에서 비효율이 발생 했으므로 Index 조정이 필요