2.31_GBEP*_(Group_By_Extension_Pruning)
2011.10.11 03:31
■ 불필요한 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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^