빅데이터과정/SQL TUNING

#36_140804_TUNING_UNION TUINING

하히후후후 2014. 8. 4. 18:15
728x90

# 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



저작자표시 비영리 변경금지 (새창열림)