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

by 정미나 2018.01.23 22:09