메뉴 건너뛰기

bysql.net

2.31_GBEP*_(Group_By_Extension_Pruning)

2011.10.11 12:31

darkbeom 조회 수:1868


■ 불필요한 ROLLUP 이나 CUBE를 삭제하라

-- GROUP BY 는 무거운 연산이다.
-- GROUPING 을 하려면 SORT 작업이 필수인데 대용량 데이터일 경우 특히 부하가 심하다.
-- ROLLUP 이나 CUBE등의 GROUP BY EXTENSOIN 기능을 사용할때 불필요한 ROLLUP 이나 CUBE를 제거한다.


ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';                
                 
SELECT E1.DEPARTMENT_ID, E1.JOB_ID, E1.AVG_SAL
  FROM (SELECT E.DEPARTMENT_ID, E.JOB_ID, AVG (E.SALARY) AVG_SAL
          FROM EMPLOYEE E
         GROUP BY ROLLUP (E.DEPARTMENT_ID, E.JOB_ID)) E1
 WHERE E1.JOB_ID = 'MK_REP';
 
DEPARTMENT_ID JOB_ID AVG_SAL
20            MK_REP 6000

  
ALTER SESSION SET EVENTS '10053 trace name context off';

--------------------------------------------------+-----------------------------------+
| Id  | Operation                     | Name      | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT              |           |       |       |     3 |           |
| 1   |  HASH GROUP BY                |           |     1 |    16 |     3 |  00:00:01 |
| 2   |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE  |     1 |    16 |     2 |  00:00:01 |
| 3   |    INDEX RANGE SCAN           | EMP_JOB_IX|     1 |       |     1 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("E"."JOB_ID"='MK_REP')


-- 이상하다 ROLLUP OPERATION이 사라졌다.
-- 아래의 쿼리는 오라클 TRANSFORMER가 다음과 같이 변환시킨것이다.


************************************
Cost-based predicate pushdown (JPPD)
************************************
... 중간생략
JPPD: Applying transformation directives
query block SEL$F5BB74E1 (#1) unchanged
FPD: Considering simple filter push in query block SEL$F5BB74E1 (#1)
"E"."JOB_ID"='MK_REP'
try to generate transitive predicate from check constraints for query block SEL$F5BB74E1 (#1)
finally: "E"."JOB_ID"='MK_REP'

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "E"."DEPARTMENT_ID" "DEPARTMENT_ID","E"."JOB_ID" "JOB_ID",AVG("E"."SALARY") "AVG_SAL"
FROM   "TLO"."EMPLOYEE" "E" WHERE "E"."JOB_ID"='MK_REP'
GROUP  BY "E"."DEPARTMENT_ID","E"."JOB_ID"

kkoqbc: optimizing query block SEL$F5BB74E1 (#1)


-- 변환된 SQL의 플랜도 같음을 알수있다. 플랜을 확인해보자


EXPLAIN PLAN FOR
SELECT E.DEPARTMENT_ID, E.JOB_ID, AVG (E.SALARY) AVG_SAL
  FROM EMPLOYEE E
 WHERE E.JOB_ID = 'MK_REP'
 GROUP BY E.DEPARTMENT_ID, JOB_ID;


SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  
 
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    16 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY               |            |     1 |    16 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |     1 |    16 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("E"."JOB_ID"='MK_REP')



-- 하지만 항상 이런 변환이 일어나는것은 아니다.

■ 제약 조건
-- ROLLUP 이나 CUBE 사용시 GROUP BY 단위에 포함된 컬럼 중맨 오른쪽의 컬럼을 인라인뷰 바깥쪽에서
   WHERE 절의 EQUAL 조건으로 사용되어야만 한다.  아래의 SQL을 살펴보자.


EXPLAIN PLAN FOR
SELECT E1.DEPARTMENT_ID, E1.JOB_ID, E1.AVG_SAL
  FROM (SELECT E.DEPARTMENT_ID, E.JOB_ID, AVG (E.SALARY) AVG_SAL
          FROM EMPLOYEE E
         GROUP BY ROLLUP (E.DEPARTMENT_ID, E.JOB_ID)) E1
 WHERE E1.DEPARTMENT_ID = 20;
 
 
DEPARTMENT_ID    JOB_ID    AVG_SAL

20        MK_MAN    13000
20        MK_REP    6000
20            9500

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |    33 |     3  (34)| 00:00:01 |
|   1 |  VIEW                         |                   |     1 |    33 |     3  (34)| 00:00:01 |
|   2 |   SORT GROUP BY ROLLUP        |                   |     1 |    16 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |     1 |    16 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("E"."DEPARTMENT_ID"=12000)



-- 반대로 ROLLUP에서 마지막 컬럼 JOB_ID가 아닌 DEPARTMENT_ID를 인라인뷰 바깥쪽에서 사용하였으므로
   변환에 실패하고 ROOLUP 이 수행 된다.
  
■ ROLLUP의 정의를 살펴보자


SELECT A, B, MAX(COL1)
WHERE  TAB
GROUP  BY ROLLUP(A, B)


-- 위의 SQL을 논리적으로 풀면 아래와 같다


SELECT A, B, MAX(COL1)
FROM   TAB
GROUP  BY A, B
UNION  ALL
SELECT A, NULL AS B, MAX(COL1)
FROM   TAB
GROUP  BY A
UNION  ALL
SELECT NULL AS A, NULL AS B, MAX(COL1)
FROM   TAB


-- 그런데 WHERE 절에 B = 상수 조건을 주면 아래와 같다.


SELECT A, B, MAX(COL1)
FROM   TAB
WHERE  B = 20
GROUP  BY A, B
UNION  ALL
SELECT A, NULL AS B, MAX(COL1)
FROM   TAB
WHERE  NULL = 20  --> 항상 논리적으로 FALSE 임
GROUP  BY A
UNION  ALL
SELECT NULL AS A, NULL AS B, MAX(COL1)
FROM   TAB
WHERE  NULL = 20  --> 항상 논리적으로 FALSE 임


-- UNION ALL의 아랫부분은 논리적으로 항상 FALSE이므로 제거되고 가장 위쪽 SQL만 남게 된다.
-- 위의 원리를 이용하여 SQL 변환이 이뤄지는데 CUBE에서도 발생된다.


EXPLAIN PLAN FOR
SELECT E1.DEPARTMENT_ID, E1.JOB_ID, E1.AVG_SAL
  FROM (SELECT E.DEPARTMENT_ID, E.JOB_ID, AVG (E.SALARY) AVG_SAL
          FROM EMPLOYEE E
         GROUP BY CUBE (E.DEPARTMENT_ID, E.JOB_ID)) E1
 WHERE E1.JOB_ID = 'MK_REP';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |    33 |     3  (34)| 00:00:01 |
|   1 |  VIEW                         |            |     1 |    33 |     3  (34)| 00:00:01 |
|   2 |   SORT GROUP BY ROLLUP        |            |     1 |    16 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |     1 |    16 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_JOB_IX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("E"."JOB_ID"='MK_REP')


-- CUBE논리적으로 가능한 모든 집합을 만드는것이다.
-- JOB_ID = 'MK_REP' 조건이 있음에도 불구하고 불필요한 쿼리블럭은 삭제되고 ROLLUP만 남게 되는것이다.


EXPLAIN PLAN FOR
SELECT *
  FROM (SELECT   E.DEPARTMENT_ID, E.JOB_ID, AVG (E.SALARY) AVG_SAL
            FROM EMPLOYEE E
        GROUP BY ROLLUP (E.JOB_ID, E.DEPARTMENT_ID)) E1
 WHERE E1.JOB_ID = 'MK_REP';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
 
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |    33 |     3  (34)| 00:00:01 |
|   1 |  VIEW                         |            |     1 |    33 |     3  (34)| 00:00:01 |
|   2 |   SORT GROUP BY ROLLUP        |            |     1 |    16 |     3  (34)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |     1 |    16 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_JOB_IX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("E"."JOB_ID"='MK_REP')



■ 그렇다면 어떻게 CUBE가 ROLLUP과 같을 수 있나?


SELECT DEPARTMENT_ID, JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  JOB_ID = 'MK_REP'
GROUP  BY JOB_ID, DEPARTMENT_ID
UNION  ALL
SELECT NULL AS DEPARTMENT_ID, JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  JOB_ID = 'MK_REP'
GROUP  BY JOB_ID
UNION  ALL
SELECT DEPARTMENT_ID, NULL AS JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  NULL = 'MK_REP'  --> 항상 논리적으로 FALSE 임
GROUP  BY DEPARTMENT_ID
UNION  ALL
SELECT NULL AS DEPARTMENT_ID, NULL AS JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  NULL = 'MK_REP'  --> 항상 논리적으로 FALSE 임
GROUP  BY NULL 


-- 논리적으로 FALSE인 집합을 제거하면


SELECT DEPARTMENT_ID, JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  JOB_ID = 'MK_REP'
GROUP  BY JOB_ID, DEPARTMENT_ID
UNION  ALL
SELECT NULL AS DEPARTMENT_ID, JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  JOB_ID = 'MK_REP'
GROUP  BY JOB_ID


-- 위의 SQL은 ROLLUP과 같음으 증명하면 모든것이 해결


SELECT DEPARTMENT_ID, JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  JOB_ID = 'MK_REP'
GROUP  BY ROLLUP(JOB_ID, DEPARTMENT_ID)


-- 위의 SQL을 논리적으로 풀면 아래와 같다


SELECT DEPARTMENT_ID, JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  JOB_ID = 'MK_REP'
GROUP  BY JOB_ID, DEPARTMENT_ID
UNION  ALL
SELECT NULL AS DEPARTMENT_ID, JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  JOB_ID = 'MK_REP'
GROUP  BY JOB_ID
UNION  ALL
SELECT DEPARTMENT_ID, NULL AS JOB_ID, AVG(SALARY) AVG_SAL
FROM   EMPLOYEE
WHERE  NULL = 'MK_REP'  --> 항상 논리적으로 FALSE 임
GROUP  BY DEPARTMENT_ID


-- 결과적으로 논리적으로 FALSE인 집합을 제거하면 ROLLUP = CUBE 관계가 자연스럽게 증명이 된다.

결론 ROLLUP은 GROUP BY 가 되고 CUBE는 ROLLUP이 된다.
-- GROUP BY 절의 ROLLUP이나 CUBE에서 가장 오른쪽 컬럼을 WHERE 절에서 EQUAL 조건으로 사용하면
   ROLLUP은 ROLLUP이 제거된 GROUP BY로 변환되고 CUBE는 ROLLUP으로 변환된다.





The Logical Optimizer  (bysql.net 2011년 2차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 10월 11일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^