ROLLUP의 기본개념

ROLLUP의 기본개념

ROLLUP은 ANSI SQL99의 대표적인 함수로 거의 모든 DBMS에서 제공하고 있습니다. rollup을 union all과 grouping_sets함수를 사용하여 동일하게 표현할수 있습니다. 이 개념을 알아야 rollup 함수가 어떤식으로 작동하는지 이해를 할수 있습니다. 아래의 예를 살펴봅니다

예제 테이블 소개

• EMP 테이블
  EMP테이블은 오라클 DBMS를 설치시 샘플스키마인 SCOTT유저의 메인 테이블중 하나로 아래와 같이 구성되어 있습니다. EMP테이블중 DEPTNO, EMPNO, SAL칼럼만 가지고 테스트해봅니다.
  

DEPTNO	EMPNO	SAL
10	7934	1300
10	7839	5000
10	7782	2450
20	7902	3000
20	7876	1100
20	7788	3000
20	7566	2975
20	7369	800
30	7900	950
30	7844	1500
30	7698	2850
30	7654	1250
30	7521	1250
30	7499	1600

• 최종결과값
  EMP테이블에서 아래와 같이 2차원 ROLLUP값을 구해봅니다
  

DEPTNO	EMPNO	SUM(SAL)
10	7782	2450
10	7839	5000
10	7934	1300
10		8750
20	7369	800
20	7566	2975
20	7788	1100
20	7876	1100
20	7902	3000
20		8975
30	7900	950
30	7499	1600
30	7521	1250
30	7654	1250
30	7698	2850
30	7844	1500
30		9400
		27125

가. 여러개의 SQL을 사용하여 최종결과값 구하기

EMP테이블에 여러개의 SQL을 사용하여 최종결과값을 구하는 방식입니다.
이 방식은 4가지 방식중 가장 최악의 방식으로 가급적 사용하지 말아야 합니다. 왜냐면
첫째. 동일 테이블에 3번 접근하여야 결과값을 만들수 있습니다.
둘째. 이방식은 Hard Pasing 문제로 인해 UNION ALL을 사용한 방식보다 좋지 않습니다.

여러개의 SQL을 사용하다보니 DBMS 엔진이 HARD PARSING 문제나 초당 발생 SQL수량이 많아져 래치경합문제를 발생시켜, DBMS 엔진에 좋지않은 문제를 발생시킬수 있습니다.
여기에서는 3개의 SQL만 사용하지만 이 SQL이 가장 많이 사용하는 화면이라면 SQL을 하나만 사용하여 동일값을 추출하는 SQL보다 성능상으로 불리하다는것입니다.

select deptno, empno, sum(sal)
from emp  
group by deptno, empno ;

select deptno, sum(sal)
from emp  <br/>
group by deptno ;

select  sum(sal)<br/>
from emp  group by ();

나. UNION ALL을 사용하여 최종결과값 구하기

이 방식은 가항보다는 좋지만 동일테이블을 계속 접근해야 하므로 성능상 좋지않은 방식이라고 생각합니다.
현장에 나가 튜닝을 진행하다 보면 이런 방식의 SQL을 많이 보는데 어떤 SQL은 동일테이블을 수십번에서 수백번 접근하여 처리하는것도 볼수 있었습니다. 당연히 성능문제가 발생하며 시스템에 악영향을 주고있었다.
그래도 가항의 방식보다 좋은것은 SQL파싱이 한번만 발생합니다는 점입니다. 이것을 이용하여 시스템 행업이 발생하는 심각한 곳들의 성능문제를 해결해 준적이 몇번있습니다.

select deptno, empno, sum(sal)
from emp
group by deptno, empno
union all
select deptno, null empno, sum(sal)
from emp
group by deptno
union all
select null deptno, null empno, sum(sal)
from emp
group by ()
order by 1, 2;

다. GROUPING SETS을 사용하여 최종결과값 구하기

이함수는 기본적으로 ROLLUP과 비슷하게 동작합니다고 생각하면 됩니다.
SQL이 DBMS OPTIMIZER에게 전달되면 내부 변환이 발생하는데 이때 오라클은 Grouping Sets TO Rollup 즉 GSTR 기능이 있어 SQL이 ROLLUP으로 전환되며, ROLLUP처럼 테이블에 한번만 접근하여 처리합니다.(실행계획상의 복잡한 방식을 여기에서 논의하지는 않겠다)

select deptno, empno, sum(sal)
from emp
Group by GROUPING SETS
             ( (deptno, empno),
               (deptno),
               () )
 order by 1;

라. ROLLUP을 사용하여 최종결과값 구하기

오라클 DBMS에서의 ROLLUP 방식은 확실하게 UNION ALL 방식보다는 확실하게 성능상 우위에 있습니다.
오라클을 제외한 기타 DBMS(마리아, MSSQL등)은 ROLLUP이 동일 테이블에 여러번 접근방식 UNION ALL로 내부적으로 처리됩니다. 그래서 성능상의 이득이 오라클보다 좋지않습니다.

select deptno, empno, sum(sal)
from emp 
Group by ROLLUP(deptno, empno)
order by 1;

마. 결언

테이블에 SLQ을 사용하여 원하는 값을 처리할때는 테이블에 최소한 접근하여 단번에 처리하는 방법을 사용하는것이 좋다.
위의 방식 4가지중 ROLLUP, GROUPING SETS 함수를 사용하는것이 가장 좋은 SQL형태라고 판단됩니다.
SQL을 만들기가 어렵다고 해서 PL/SQL을 사용하여 루핑형 SQL형태로 처리하거나, SQL을 여러개로 분할하여 처리하거나, UNION ALL등을 사용하여 동일테이블에 반복 접근하는 방식들은 성능상 문제가 발생할 여지가 큽니다.
그러므로 ANSISQL 99표준에서 제공하는 함수들을 사용하여 처리하거나 WITH, DECODE, CASE등의 고급 처리기술을 이용하여 SQL을 만드는것을 권고합니다. 향후 이방법에 대해 포스팅하도록 하겠습니다.