[Oracle] 오라클 WITH절 사용법

with절이란

오라클9 이후 버전부터 사용이 가능하며 이름이 부여된 서브쿼리, 임시테이블을 만든다는 관점에서 보면 VIEW와 쓰임새가 비슷한데 차이점이 있다면 VIEW는 한 번 만들어 놓으면 DROP할 때까지 없어지지 않고, with절의 경우 한 번 실행할 쿼리문내에 정의되어 있을 경우, 그 쿼리문 안에서만 실행된다는 차이점이 있다.

사용이유

반복적으로 SQL문을 사용하는 경우 그 블록에 이름을 부여하여 재사용할 수 있게 함으로서 쿼리 성능을 높일 수 있는데, with절을 이용해 미리 이름을 부여해서 쿼리 블록을 만들 수 있음.

• with절 사용안한 예

select job, sum(sal) as 토탈
from emp
group by job
having sum(sal) > (select avg(sum(sal))
                                    from emp
                                    group by job);

• with절 사용한 예

with job_sumsal as (select job, sum(sal) as 토탈
                                        from emp
                                        group by job)
select job, 토탈
from job_sumsal
where 토탈 > (select avg(토탈) from job_sumsal);

동일한 SQL을 with절을 사용하지 않은 문은 시간이 2배정도 더 걸리고, with절을 사용한 절이 성능이 더 높다는 것을 알 수 있다.

with절로 작성한다면 얻은 데이터 들을 임시 저장 영역에 저장하고, 그 데이터를 job_sumsal로 가져오면 되기 때문에 시간이 절반으로 줄어듦

서브쿼리 팩토링(Subquery Factoring)

• 특정 서브 쿼리문의 컬럼을 다른 서브 쿼리문에서 참조하는 것이 가능
• from절의 서브쿼리로는 불가능

-- 직업별 토탈 값의 평균 값에 3000을 더한 값보다 더 큰 부서 번호별 토탈 월급들을 출력

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;