[Oracle] 오라클 SQL 롤업 ROLLUP, 데이타베이스 튜닝 집계함수

정미나 2018. 1. 23. 22:09

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;

* 실행계획

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 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;

* 실행계획

--------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------

| 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) */ 추가

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 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이 더 효율적이라고 할 수 있음

저작자표시 비영리 변경금지