정미나닷컴
[Oracle] 오라클 SQL 롤업 ROLLUP, 데이타베이스 튜닝 집계함수 본문
2012년 1월에 팔린 상품의 일별 상품별 판매수량과 판매금액 및 각각의 랭킹을 구하고 일별, 월별 총계 구하기
* 결과값
：
：
：
* 쿼리1
SELECT CASE WHEN GROUPING(O.ORD_DT)=1 AND GROUPING(I.ITEM_ID)=1 THEN '201201'
ELSE O.ORD_DT END ORD_DT,
I.ITEM_ID,
CASE WHEN GROUPING(O.ORD_DT)=1 AND GROUPING(I.ITEM_ID)=1 THEN '합계'
WHEN GROUPING(I.ITEM_ID)=1 THEN '소계'
ELSE I.ITEM_NM END ITEM_NM,
SUM(O.ORD_ITEM_QTY) QTY,
SUM(O.ORD_ITEM_QTY*P.PRICE) PRICE,
CASE WHEN GROUPING(O.ORD_DT)=1 AND GROUPING(I.ITEM_ID)=1 THEN ''
WHEN GROUPING(I.ITEM_ID)=1 THEN ''
ELSE TO_CHAR(RANK() OVER
(PARTITION BY O.ORD_DT ORDER BY SUM(O.ORD_ITEM_QTY) DESC)-1) END QTY_RANK,
CASE WHEN GROUPING(O.ORD_DT)=1 AND GROUPING(I.ITEM_ID)=1 THEN ''
WHEN GROUPING(I.ITEM_ID)=1 THEN ''
ELSE TO_CHAR(RANK() OVER
(PARTITION BY O.ORD_DT ORDER BY SUM(O.ORD_ITEM_QTY*P.PRICE) DESC)-1) END PRICE_RANK
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 ROLLUP(O.ORD_DT,(I.ITEM_ID, I.ITEM_NM))
ORDER BY O.ORD_DT, ITEM_ID;
* 실행계획
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2884 |00:00:02.62 | 43424 |
| 1 | SORT ORDER BY | | 1 | 2884 |00:00:02.62 | 43424 |
| 2 | WINDOW SORT | | 1 | 2884 |00:00:02.62 | 43424 |
| 3 | WINDOW SORT | | 1 | 2884 |00:00:02.61 | 43424 |
| 4 | SORT GROUP BY ROLLUP | | 1 | 2884 |00:00:02.61 | 43424 |
|* 5 | HASH JOIN | | 1 | 1546K|00:00:01.46 | 43424 |
| 6 | MERGE JOIN | | 1 | 92 |00:00:00.01 | 5 |
| 7 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 92 |00:00:00.01 | 2 |
| 8 | INDEX FULL SCAN | ITEM_PK | 1 | 92 |00:00:00.01 | 1 |
|* 9 | SORT JOIN | | 92 | 92 |00:00:00.01 | 3 |
| 10 | VIEW | | 1 | 92 |00:00:00.01 | 3 |
| 11 | HASH GROUP BY | | 1 | 92 |00:00:00.01 | 3 |
| 12 | TABLE ACCESS BY INDEX ROWID| UITEM_PRICE | 1 | 130 |00:00:00.01 | 3 |
| 13 | INDEX FULL SCAN | UITEM_PRICE_PK | 1 | 130 |00:00:00.01 | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | ORD_ITEM | 1 | 1546K|00:00:00.81 | 43419 |
|* 15 | INDEX RANGE SCAN | ORD_ITEM_X01 | 1 | 1546K|00:00:00.22 | 5815 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("O"."ITEM_ID"="I"."ITEM_ID")
9 - access("I"."ITEM_ID"="P"."ITEM_ID")
filter("I"."ITEM_ID"="P"."ITEM_ID")
15 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')
※ TABLE RANDOM ACCESS 하는 과정에서 비효율이 발생하지 않음에도 불구하고(버려지는 row가 없음에도 불구하고) 블록 I/O가 현저하게 많을 때는(주로 클러스터링 팩터가 안좋은 경우) 인덱스를 재구성하는 것이 효율적 > 14, 15번
* 쿼리2
CREATE INDEX "ORD_ITEM_X02" ON "ORD_ITEM" ("ORD_DT", "ITEM_ID", "ORD_ITEM_QTY")
SELECT CASE WHEN GROUPING(T.ORD_DT)=1 AND GROUPING(T.ITEM_ID)=1 THEN '201201'
ELSE T.ORD_DT END ORD_DT,
T.ITEM_ID,
CASE WHEN GROUPING(T.ORD_DT)=1 AND GROUPING(T.ITEM_ID)=1 THEN '합계'
WHEN GROUPING(T.ITEM_ID)=1 THEN '소계'
ELSE T.ITEM_NM END ITEM_NM,
SUM(T.QTY) QTY,
SUM(T.PRICE) PRICE,
CASE WHEN GROUPING(T.ORD_DT)=1 AND GROUPING(T.ITEM_ID)=1 THEN ''
WHEN GROUPING(T.ITEM_ID)=1 THEN ''
ELSE MAX(TO_CHAR(T.QTY_RANK)) END QTY_RANK,
CASE WHEN GROUPING(T.ORD_DT)=1 AND GROUPING(T.ITEM_ID)=1 THEN ''
WHEN GROUPING(T.ITEM_ID)=1 THEN ''
ELSE MAX(TO_CHAR(T.PRICE_RANK)) END PRICE_RANK
FROM
(
SELECT
O.ORD_DT,
O.ITEM_ID,
I.ITEM_NM,
O.QTY,
O.QTY*P.PRICE PRICE,
RANK() OVER(PARTITION BY O.ORD_DT ORDER BY O.QTY DESC) QTY_RANK,
RANK() OVER(PARTITION BY O.ORD_DT ORDER BY O.QTY*P.PRICE DESC) PRICE_RANK
FROM
(
SELECT O.ORD_DT,
O.ITEM_ID,
SUM(O.ORD_ITEM_QTY) QTY
FROM ORD_ITEM O
WHERE O.ORD_DT BETWEEN '20120101' AND '20120131'
GROUP BY O.ORD_DT, O.ITEM_ID
) 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
) T
GROUP BY ROLLUP(T.ORD_DT,(T.ITEM_ID, T.ITEM_NM))
ORDER BY T.ORD_DT, T.ITEM_ID;
* 실행계획
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2884 |00:00:00.47 | 5609 |
| 1 | SORT GROUP BY ROLLUP | | 1 | 2884 |00:00:00.47 | 5609 |
| 2 | VIEW | | 1 | 2852 |00:00:00.46 | 5609 |
| 3 | WINDOW SORT | | 1 | 2852 |00:00:00.46 | 5609 |
| 4 | WINDOW SORT | | 1 | 2852 |00:00:00.46 | 5609 |
|* 5 | HASH JOIN | | 1 | 2852 |00:00:00.45 | 5609 |
| 6 | MERGE JOIN | | 1 | 92 |00:00:00.01 | 5 |
| 7 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 92 |00:00:00.01 | 2 |
| 8 | INDEX FULL SCAN | ITEM_PK | 1 | 92 |00:00:00.01 | 1 |
|* 9 | SORT JOIN | | 92 | 92 |00:00:00.01 | 3 |
| 10 | VIEW | | 1 | 92 |00:00:00.01 | 3 |
| 11 | HASH GROUP BY | | 1 | 92 |00:00:00.01 | 3 |
| 12 | TABLE ACCESS BY INDEX ROWID| UITEM_PRICE | 1 | 130 |00:00:00.01 | 3 |
| 13 | INDEX FULL SCAN | UITEM_PRICE_PK | 1 | 130 |00:00:00.01 | 1 |
| 14 | VIEW | | 1 | 2852 |00:00:00.45 | 5604 |
| 15 | HASH GROUP BY | | 1 | 2852 |00:00:00.45 | 5604 |
|* 16 | INDEX RANGE SCAN | ORD_ITEM_X02 | 1 | 1546K|00:00:00.21 | 5604 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("O"."ITEM_ID"="I"."ITEM_ID")
9 - access("I"."ITEM_ID"="P"."ITEM_ID")
filter("I"."ITEM_ID"="P"."ITEM_ID")
16 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')
※ 힌트를 이용하여 NL JOIN을 유도한 경우 실행계획
/*+ ordered use_nl(I P) */ 추가
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2884 |00:00:00.47 | 8463 |
| 1 | SORT GROUP BY ROLLUP | | 1 | 2884 |00:00:00.47 | 8463 |
| 2 | VIEW | | 1 | 2852 |00:00:00.46 | 8463 |
| 3 | WINDOW SORT | | 1 | 2852 |00:00:00.46 | 8463 |
| 4 | WINDOW SORT | | 1 | 2852 |00:00:00.45 | 8463 |
| 5 | NESTED LOOPS | | 1 | 2852 |00:00:00.45 | 8463 |
| 6 | NESTED LOOPS | | 1 | 2852 |00:00:00.40 | 8460 |
| 7 | VIEW | | 1 | 2852 |00:00:00.39 | 5604 |
| 8 | HASH GROUP BY | | 1 | 2852 |00:00:00.39 | 5604 |
|* 9 | INDEX RANGE SCAN | ORD_ITEM_X02 | 1 | 1546K|00:00:00.18 | 5604 |
| 10 | TABLE ACCESS BY INDEX ROWID | ITEM | 2852 | 2852 |00:00:00.01 | 2856 |
|* 11 | INDEX UNIQUE SCAN | ITEM_PK | 2852 | 2852 |00:00:00.01 | 4 |
|* 12 | VIEW | | 2852 | 2852 |00:00:00.05 | 3 |
| 13 | SORT GROUP BY | | 2852 | 262K|00:00:00.04 | 3 |
| 14 | TABLE ACCESS BY INDEX ROWID| UITEM_PRICE | 1 | 130 |00:00:00.01 | 3 |
| 15 | INDEX FULL SCAN | UITEM_PRICE_PK | 1 | 130 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')
11 - access("O"."ITEM_ID"="I"."ITEM_ID")
12 - filter("I"."ITEM_ID"="P"."ITEM_ID")
☞ HASH Join이 더 효율적이라고 할 수 있음
'programming' 카테고리의 다른 글
| [Oracle] 오라클 다중 버전 읽기 일관성 모델, Multi-Version Read Consistency Model (1) | 2018.03.28 |
|---|---|
| [Oracle] 오라클 SQL 튜닝, 최종 출력 건에 대해서만 JOIN, 스칼라 서브 쿼리 (1) | 2018.02.02 |
| [Oracle] 오라클 SQL 튜닝, 집계함수, 누적 구하기, JOIN, GROUP BY (0) | 2018.01.14 |
| [Oracle] 오라클 Direct Path I/O, Direct Path Insert (0) | 2018.01.09 |
| [Oracle] 오라클 서버 환경 셋팅, Oracle 11g, SQL-Developer 설치 (0) | 2018.01.07 |
