[Oracle] 확장 GROUP 함수 - CUBE()

 

 

 CUBE() 함수는 인자로 받는 컬럼으로 만들 수 있는 모든 조합으로 집계하여 추출하는 함수이다.

 

위와 같은 결과를 얻으려면, 다음과 같이 작성할 수 있다.

SELECT B.DNAME
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY CUBE(B.DNAME)
 ORDER BY 1
;

 얼핏보면 ROLLUP() 함수와 다를 거 없는 함수처럼 보이지만, CUBE() 함수는 받는 인자가 많아질수록 조합이 다양해져 출력하는 결과가 훨씬 많아진다.

 

SELECT B.DNAME
     , A.JOB
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY CUBE(B.DNAME, A.JOB)
 ORDER BY 1,2
;

위와 같이 쿼리를 작성하게 될 경우, 아래와 같은 결과 값을 얻을 수 있다.

 

컬럼 A (A1, A2, A3)	컬럼 B (B1, B2, B3)	집계 함수(SUM(C))
A1	B1	A1별 B1별 C의 총합
A1	B2	A1별 B2별 C의 총합
A1	B3	A1별 B3별 C의 총합
A1		A1별 C의 총합
A2	B1	A2별 B1별 C의 총합
A2	B2	A2별 B2별 C의 총합
A2	B3	A2별 B3별 C의 총합
A2		A2별 C의 총합
A3	B1	A3별 B1별 C의 총합
A3	B2	A3별 B2별 C의 총합
A3	B3	A3별 B3별 C의 총합
A3		A3별 C의 총합
		C의 총합

이를 GROUPING SETS() 함수로 표현하면 더 이해하기 쉬워진다.

SELECT B.DNAME
     , A.JOB
     , SUM(A.SAL)
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
 GROUP BY GROUPING SETS((B.DNAME, A.JOB), B.DNAME, A.JOB, ())
 ORDER BY 1,2
;

 

 CUBE() 함수는 확장 GROUP 함수 중에 가장 사용 빈도 수가 적은 함수라고 알고있다. 인수를 많이 줄 수록 데이터가 뻥튀기 되어 원하는 결과를 찾는 것도 일이 되는 느낌이다. 사용 환경에 맞는 함수를 적절히 사용하는 것이 중요할 것 같다.

---

 

 확장 GROUP 함수는 필수는 아니지만 알아두면 굉장히 유용하게 쓸 수 있는 함수이다. 단순히 원하는 값을 손 쉽게 얻을 수 있다는 것을 떠나서 성능적인 측면에서도 많은 도움을 준다. 비슷한 역할을 하는 ROLLUP() 함수의 사용 유무 비교를 오라클 성능 분석표를 통해 보면 확실히 알 수 있다.

 여기서 중요하게 볼 부분은 Elapsed Time과 Query 컬럼이다. Elapsed Time은 쿼리를 실행하는데 걸린 시간이고, Query는 결과를 얻기까지 시스템이 돌린 쿼리의 횟수이다. 데이터가 적고 ERD 규모가 적은 환경에서 테스트해보았는데도 불구하고 한 눈에 봐도 차이가 나는 것이 보인다. 이런 차이는 실무에서 방대한 규모의 데이터를 다룬다면 더 뚜렷하게 보일 것이다.
 본인이 소규모 프로젝트만 진행하고 당장 급하게 쿼리를 짜야하는 상황이라면 이런 성능표까지 비교분석하면서 쿼리를 짤 필요는 없을 것이다(결과가 잘나오는 것이 가장 중요하니까). 하지만 막대한 양의 데이터를 다루는 큰 규모의 프로젝트에서 쿼리를 짠다거나, 튜너의 업무를 맡게 되었다면 이런 성능 분석을 통해 최대한 메모리 소모를 적게하는 코드 방식을 고민해 볼 필요가 있다고 생각한다.

 

[Oracle] 확장 GROUP 함수 - ROLLUP()

 

[Oracle] 확장 GROUP 함수 - GROUPING SETS()