[Oracle] WITH ~ AS 절

하나의 SQL 에서 비슷한 select 문장이 반복되어서 나타나는 경우, with 절로 작성하면 성능을 개선할 수 있다.

단, 여러 개의 with 절을 동시에 여러 사람이 한꺼번에 수행하면 다 같이 느려진다.

 

 

 

 

예제 1. 1부터 10까지의 숫자 중에 짝수만 출력

with table_10 as ( select level as num
                        from dual
                        connect by level <= 10 )                        
select num
    from table_10
    where mod(num, 2) = 0;

with 절에서 table_10 이라는 임시 테이블을 생성하고 아래의 쿼리에서 그 테이블을 사용한다.

그리고 with 절이 끝나면 임시 테이블은 사라진다.

 

 

 

예제 2. 주사위를 10번 던져서 10개의 숫자를 출력

with dice1 as ( select round(dbms_random.value(0.5, 6.5)) as num1
                 from dual )
select num1
    from dice1
    connect by level <= 10;

with 절에서 주사위를 한 개 테이블로 만들고, 그 아래의 쿼리에서 만든 주사위 테이블을 사용한다.

 

 

 

예제 3. 주사위를 100번 던져서 숫자가 3이 나올 확률

with dice1 as ( select round(dbms_random.value(0.5, 6.5)) as num1
                 from dual 
                 connect by level <= 100 )
select count(*) / 100
    from dice1
    where num1 = 3;

 

 

 

예제 4. 아래의 SQL을 튜닝

-- 튜닝 전
select e1.*, e2.*
    from ( select deptno, sum(sal) as sumsal
                from emp
                where deptno != 20
                group by deptno ) e1,
         ( select deptno, sum(sal) as sumsal
                from emp
                where deptno != 30
                group by deptno ) e2
    where e1.deptno = e2.deptno;

-- 튜닝 후
with emp500 as ( select deptno as 부서번호, sum(sal) as 토탈월급
                    from emp
                    group by deptno )
select e1.*, e2.*
    from emp500 e1, emp500 e2
    where e1.부서번호 = e2.부서번호
        and e1.부서번호 != 20
        and e2.부서번호 != 30;

 

 

 

with 절 사용 시 중요한 힌트

1. /*+ inline */ : temp table 을 구성 안 하겠다.

   어쩔 수 없이 with 절을 동시에 수행할 수 밖에 없는 환경에서

   temp 테이블을 구성하지 않고 with 절을 수행하고 싶다면 inline 힌트를 쓰면 된다.

2. /*+ materialize */ : temp table 을 구성 하겠다.

with emp500 as ( select /*+ inline */ deptno as 부서번호, sum(sal) as 토탈월급
                    from emp
                    group by deptno )
select e1.*, e2.*
    from emp500 e1, emp500 e2
    where e1.부서번호 = e2.부서번호
        and e1.부서번호 != 20
        and e2.부서번호 != 30;

 

 

 

예제 5. 아래의 에러가 나는 SQL의 결과를 구현할 수 있도록 with 절로 변경

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

SQL 명령어가 올바르게 종료되지 않았습니다.

위의 SQL 은 실행되지 않는다. 위의 결과는 FROM 절의 서브 쿼리로는 할 수 없다.

위의 경우는 WITH 절을 사용해서 WITH 절 내에서 임시 테이블을 구성해서 수행해야 한다.

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;

위의 SQL 현상을 오라클 용어로 subquery factoring 이라고 한다.

즉, job_sumsal 이라는 임시 테이블을 구성하고 다음 임시 테이블을 구성하는 쿼리에서 가져다 쓰는 것을 말한다.