[SQL] 20. With절, 임시 테이블

20. With절, 임시 테이블

SQL을 통해 빅데이터라 불리는 매우 크고 무거운 데이터를 다루는데,
새로운 결과를 원하고 저장할 때마다 table을 저장할 수 없다.
또한, 실무에서는 실제 실행 속도도 빠르게 진행되어야 하므로
SQL에는 임시 테이블을 만드는 다양한 방법이 존재한다.

with절은 동일한 SQL이 반복되어서 사용될 때 성능을 높이기 위해 사용된다.
table을 만들지 않고도 table 만든 것과 같은 효과를 내는데,
실제로는 temp라는 임시 테이블에 저장되는 것이다.

사용법은
with 임시 테이블명 as ( 임시 테이블에 넣을 데이터)
임시 테이블을 이용해 사용할 쿼리 이다.

실제 예제와 함께 알아보자.

예제) 사원(emp) 테이블에서 직업별 월급 합산분을 출력하는데 직업별 월급 합산분의 평균보다 높은 것만 출력하시오!

ⓐ with절 사용하지 않은 쿼리
select job, sum(sal)
from emp
group by job
having sum(sal) > (select avg(sum(sal)) from emp group by job) ;

ⓑ with절 사용한 쿼리
with eee as
(select job, sum(sal) as total from emp group by job)
select job, total from eee where total > (select avg(total) from eee);

결과는 동일하다. 하지만 with절을 사용하는 이유이자 with절의 장점은

1. temp라는 임시 테이블을 사용해서 장시간 걸리는 쿼리의 결과를 저장해놓고 저장해놓은 데이터를 엑세스하기 때문에 성능이 좋다.
2. 임시로 테이블을 생성해서 쿼리를 해야할때에 굳이 테이블을 생성하지 않고 with절을 사용해서 임시로 테이블을 생성하면 된다.

그렇다면 모두 with절로 사용하면 되지 않는가?라는 질문에 답하면 아니다.
with절을 너무 남발해서 같은 시간에 여러 개의 with절을 동시에 돌리면 temp(임시 테이블)가 견딜 수 있는 정도가 넘어가서 다같이 느려진다.

이를 위해 with절에는 두 가지 종류가 있다.

materialize, inline

첫번째로 materialize는 기본 with절 사용 시 적용되는 것으로 temp(임시 테이블)를 사용한다.
그래서 실행시간이 단축된다.

가끔 자동적으로 materialize가 적용되지 않는 경우가 있어 기재하는 것을 권장한다.

두번째로 inline은 temp 테이블도 생성하지 않고 in line view로 수행하는 것이다.
실행방법은 with절 사용 시 select 뒤에 /*+materialize */, /*+inline */ 를 붙여준다.

예시) with eee as
(select /*+inline */ job, sum(sal) as total from emp group by job)
select job, total from eee where total > (select avg(total) from eee);

임시 테이블 사용법

with절 이외에 테이블을 만들때 아에 임시 테이블로 만드는 방법도 있다.
다만 "임시"라는 의미에 맞게 상 언제까지 저장되는 테이블이라는 조건이 있어야 하는데,

1. commit 할 때 까지 : on commit delete rows
2. 세션이 종료될 때까지 : on commit preserve rows
   

으로 구분할 수 있다.

예제) commit하면 사라지는 임시 테이블 emp_temp를 만드시오!
create global temporary table emp_temp
on commit delete rows
as
select *
from emp
where 1=2 ; #table의 틀만 가져오겠다.

where 1=2는 True가 아니다. 따라서 조건문이 해당되는 데이터가 없으므로
현재 from에 있는 table의 틀(컬럼 수 등)만 가져오겠다. 라는 의미이다.

이후 데이터를 모두 집어넣고
insert into emp_temp
select * from emp;

select from emp_temp ; ← 데이터 정상 입력 확인!
commit ;
select from emp_temp ; ← 데이터가 없다!

세션이 종료된다는 것은 exit, 즉 sql을 on/off하는 것을 의미한다.

=================이 글은 Markdown을 활용해 작성하였습니다=================