ORACLE ROLLUP v1.0
from
1. ORACLE ROLLUP
GROUP BY 는 특정 값에대한 집계값을 얻고자 할 때 사용한다. 때로는 이 값에 TOTAL 값이 필요할 때가 있다. 이럴 때 ROLLUP 을 사용하면 편하게 값을 얻을 수 있다.
2. ROLLUP 예제
<ROLLUP>
select to_char(count(first_name)) num,department_name
from emp,dept
where emp.department_id = dept.department_id
group by department_name
union all
select to_char(sum(num)), 'TOTAL'
from
(select count(first_name) num,department_name
from emp,dept
where emp.department_id = dept.department_id
group by department_name)
/
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 5843 | 8 (63)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | HASH GROUP BY | | 106 | 5830 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 30 | 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 106 | 5830 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 107 | 2675 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | DEPT_IX | 1 | | 0 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 13 | | |
| 8 | VIEW | | 106 | 1378 | 4 (25)| 00:00:01 |
| 9 | HASH GROUP BY | | 106 | 5830 | 4 (25)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 106 | 5830 | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | EMP | 107 | 2675 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | DEPT_IX | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID")
13 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID")
쿼리의 내용을 요약하면 아래와 같다.
GROUP BY 를 사용하여 각 부서별로 몇 명의 사원이 있는지 구했고 모든부서에 대한 총합을 구하기
위해 UNION ALL 을 사용하여 실질적으로 같은 쿼리를 한번 더 수행하였다.
위에 적색으로 표시한 부분을 보면 상/하가 중복되는 것을 알 수 있다.
한마디로 같은 일이 중복되어 수행되었다.
select to_char(count(first_name)) num,department_name
from emp,dept
where emp.department_id = dept.department_id
group by rollup(department_name)
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 5830 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY ROLLUP | | 106 | 5830 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 106 | 5830 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 107 | 2675 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_IX | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID")
위의 실행계획은 ROLLUP 을 사용한 경우이다.
앞서 살펴본 UNION ALL 의 경우와 비교해 오퍼레이션이 절반으로 줄어든 걸 확인할 수 있다.
HASH GROUP BY 가 SORT GOUP BY ROOLUP 으로 변경된 걸 알 수 있다.
나머지 오퍼레이션은 코스트가 달라질 항목이 없다.
결과적으로 ROLL UP을 사용해 달라진 점은 GROUP BY OPERATION 이 HASH 에서 SORT 로 바뀌었다는
것과 1~5 에 해당하는 과정이 한번 더 반복(UNION ALL) 되지 않고 한번에 완료되었다는 점이다.
<WITH>
with v_tmp as
(select to_char(count(first_name)) num,department_name
from emp,dept
where emp.department_id = dept.department_id
group by department_name)
select num,department_name
from v_tmp
union all
select to_char(sum(num)), 'TOTAL'
from
(select num,department_name
from v_tmp)
/
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | 264K| 264K| 264K (0)|
| 3 | HASH GROUP BY | | 106 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | | | |
| 5 | NESTED LOOPS | | 106 | | | |
| 6 | TABLE ACCESS FULL | EMP | 107 | | | |
|* 7 | INDEX RANGE SCAN | DEPT_IX | 1 | | | |
| 8 | UNION-ALL | | | | | |
| 9 | VIEW | | 106 | | | |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_CC44B | 106 | | | |
| 11 | SORT AGGREGATE | | 1 | | | |
| 12 | VIEW | | 106 | | | |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_CC44B | 106 | | | |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID")
앞서 본 ROLLUP 을 사용하는 방법과 달리 서브쿼리 팩토링을 이용한 방법이다.
플랜을 보면 오퍼레이션이 더 늘어난 걸 확인할 수 있다.
파란색으로 표시한 부분은 이 SQL 에서 사용할 임시 뷰를 생성하는 부분이다. (WITH 절)
이 과정을 보면 ROLLUP OPERATION 과 대부분 일치하는 것을 확인할 수 있다.
(SORT GROUP BY 과정은 위에선 없다.)
위에서 생성된 결과값을 UNION-ALL 을 중심으로 다시 사용된다. (빨간색부분)
한번은 FULL SCAN 을 하고 다른 한번은 그룹함수를 적용했다.
/*+ ROLLUP 과 WITH 간의 성능비교 */
