[실무에서 바로 쓰는 SQL 기본과 SQL 튜닝][ORACLE] 22강

WITH절 사용하기

메인 쿼리와 서브 쿼리문을 실행할 때의 시간이 오래 걸릴 때에 사용함

SELECT job, sum(sal)
FROM emp
GROUP BY job
having sum(sal) > (select avg(sum(sal))
					from emp
                    group by job);

20분(메인 쿼리) + 20분(서브 쿼리) = 40분

아래와 같이 작성하면, 실행 시간 단축 (동일한 결과)

WITH job_subsal as (SELECT job, sum(sal) as 토탈
					FROM emp
                    GROUP BY job)
SELECT job, 토탈
FROM job_subsal
WHERE 토탈 > (SELECT avg(토탈)
				FROM job_subsal);

job_subsal : 임시 테이블명 (as절의 select 결과를 job_subsal의 데이터로 함)

아래 쿼리문에서 사용된 후, 사라지는 테이블

with절에서 20분을 소요하고, 아래에서는 이미 생성된 테이블에 대해서 쿼리문을 실행하는 것이므로 빠르게 수행됨

with절을 너무 많이 남발하면, 디스크 공간에 많은 temp table이 생겨남... (주의)

부서번호별 토탈월급을 출력하는데 부서번호별 토탈월급들의 평균값보다 더 큰 것만 출력

with dept_sumsal as (select deptno, sum(sal) as sumsal
from emp
group by deptno)
select deptno, sumsal
from dept_sumsal
where sumsal > (select avg(sumsal)
from dept_sumsal);

WITH절 - SUBQUERY FACTORING

FROM절 서브쿼리로는 구현할수 없는 부분을 구현 가능하게 함

WITH job_sumsal as (select job, sum(sal) 토탈
from emp
group by job),
deptno_sumsal as (select deptno, sum(sal) 토탈
from emp
group by deptno
having sum(sal) > (select avg(토탈) + 3000
from job_sumsal)

                   )

select deptno, 토탈
from deptno_sumsal;

입사한 년도와 입사한 년도별 토탈월급을 출력하는데 부서번호별 토탈월급들의 평균값보다 더 큰 것만 출력

with deptno_sumsal as (select deptno, sum(sal) 토탈
from emp
group by deptno),
hire_sumsal as (select to_char(hiredate,'RRRR') hire_year, sum(sal) 토탈
from emp
group by to_char(hiredate,'RRRR)
having sum(sal) > (select avg(토탈)
from deptno_sumsal)
)
select hire_year, 토탈
from hire_sumsal;