#36_140804_TUNING_UNION TUINING

# UNION TUINING

문제76. 직업, 직업별 토탈월급을 출력하는데 맨 아래쪽에 전체 토탈월급도 출력하시오

# 튜닝 전
SELECT job, SUM(sal) FROM EMP GROUP BY job UNION ALL SELECT NULL AS job, SUM(sal) FROM EMP;	db block gets        0 consistent gets       53 physical reads        0
# 튜닝 후
SELECT job, SUM(sal) FROM EMP GROUP BY ROLLUP(job);	db block gets        0 consistent gets       46 physical reads        0

문제77. 부서번호, 부서번호별 인원수를 출력하는데 맨 아래쪽에 전체 인원수도 출력하시오

# 튜닝 전
SELECT deptno, COUNT(*) FROM EMP GROUP BY deptno UNION ALL SELECT NULL AS detpno, COUNT(*) FROM EMP;	db block gets        0 consistent gets       53 physical reads        0
# 튜닝 후
SELECT deptno, COUNT(*) FROM EMP GROUP BY ROLLUP(deptno);	db block gets        0 consistent gets       46 physical reads        0

문제78. 아래의 결과를 grouping sets로 수행해서 결과를 보시오

# 튜닝 전
SELECT deptno, COUNT(*) FROM EMP GROUP BY deptno UNION ALL SELECT NULL AS detpno, COUNT(*) FROM EMP;	db block gets        0 consistent gets       53 physical reads        0
# 튜닝 후
SELECT deptno, COUNT(*) FROM EMP GROUP BY GROUPING sets((deptno),());	db block gets        0 consistent gets       46 physical reads        0

문제79. 아래의 SQL을 grouping sets로 변경하시오

# 튜닝 전
select deptno, job, avg(sal) from emp group by grouping sets((deptno),(job)); - 메모리를 많이 잡아먹는다	db block gets        24 consistent gets       430 physical reads        3
# 튜닝 후
SELECT deptno, NULL AS job, AVG(sal) FROM EMP GROUP BY deptno UNION ALL SELECT NULL AS deptno, job, AVG(sal) FROM EMP GROUP BY job;	db block gets        0 consistent gets       53 physical reads        0
select /*+ expand_gset_to_union */ deptno, job, avg(sal) from emp group by grouping sets((deptno),(job)); - expand_get_to_union 힌트 : grouping sets를 union all 로 변경하라 - grouping sets는 메모리를 많이 잡아먹어서 union all 보다 성능이 떨어진다 - 하지만 외관상 grouping sets가 깔끔하기 때문에 굳이 쓰고 싶다면 위처럼 expand_gset_to_union 힌트를 써주면 된다	db block gets        0 consistent gets       53 physical reads        0