SQL -WITH 절 (가상테이블)

with 절 작성시 주의해야할 사항

• 여러개의 with 절을 동시에 수행하면 모두 다 느려진다.
• 디스크에는 temp(임시) 로 사용할 수 있는 공간이 한정되어있다.
• 이 temp 의 공간에 여러개의 with 절이 쓰려고 하면 몇개의 with 절만 쓰고 나머지는 대기해야하므로 시간이 오히려 더 늘어나게 된다.

with 절 사용시 중요한 힌트 2가지

1. inline : temp table 구성 안하겠다.
2. materialize table 구성 하겠다.

---

with 절의 장점.

1. 대용량 데이터를 조회하는 하나의 SQL 안에서 반복적으로 사용되는 쿼리문이 존재할때 성능을 높이기 위한 방법으로 WITH 절을 사용하면 유용하다.
2. 간단한 테스트를 할 때 굳이 테이블을 생성하지 않고 WITH 절로 임시 테이블을 만들어서 테스트를 진행 할 수 있다.

---

집합연산자에서는 with 절을 한번만 사용할 수 있다. (구구단 출력 시) 알고리즘 문제 확인

예제 109 번

(1) 튜닝전 SQL

(SUB 쿼리가 많은 SQL 이라 한 쿼리당 수행속도가 20분이나 오래 걸린다. 또한 같은 내용의 SELECT 문이 반복되는 상황이다.)

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;

(2) 튜닝후 SQL (반복되는 SUB 쿼리문을 WITH 절로써 사용해 주었다. )

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 절로 temp table (임시테이블)을 생성해 주었다. 이름은 emp500
원래 temporary (임시테이블은 ) 만드는것은 create 인데 dba담당자들은 create하는걸 좋아하지않는다.
그래서 with 절로 구현해 주는것 이다.
→ 임시테이블이 유지되는 기간은 with 절이 끝날때 까지만 이다.
→ 튜닝전 sql은 40분이 걸리는 SQL 이였는데 WITH 절을 쓰게되면 부서번호, 부서번호별 토탈월급을 20분이 걸려서 출력하고 그 결과를 임시테이블로 생성하여 (EMP500) 조인하여 출력해서 20분으로 단축 되었다.

위의 WITH 절 SQL 의 실행계획을 확인

**explain plan for**

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;

**select * from table (dbms_xplan.display);**

1.번의 temp table transformation 을 해석하면 임시테이블이 생성됬다는걸 알 수 있다.
그리고 7번과 9 번에 템프테이블이 스캔 된걸 확인 할 수 있다.

그러나!

with 절 이 여러개가 작성 되면 다같이 느려진다.
with 절이 여러개가 될것 같으면 다시 sub 쿼리문으로 읽었던 sql 이 더 빠를 수 있다.
그럴때에는 sub 쿼리문을 다시 작성하는게 아니라 with 문에 hint 로 temp table 을 사용하지 않도록 하면 된다.

explain plan for

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;

select * from table (dbms_xplan.display);

→ hint 를 주어서 tmep table 을 sub query 로 다시 바꾸지 않고 inline 으로 읽게 했다.
-: sub 쿼리를 재작성하는 노동은 하지않길...

문제 2 * 2 = 4 를 sql 로 구현 하시오 

튜닝전

select '2* ' ||num || '=' || num *2
FROM (
             select level as num
            from dual
            connect by level <=10);

튜닝후

with emp10 as (select level as num
                from dual
               connect by level <=10)
select '2  *'|| num||' = ' ||num*2
from emp10;

 

→ 임시테이블을 생성해달라고 dba에게 요청하지 않아도 그냥 내가 짠 SQL 에서 WITH 절로 임시테이블을 생성할 수 있는 장점이 있다.