[MYSQL] 📚 WITH (임시 테이블 생성)

 

WITH (임시 테이블)

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

WITH TEMP_TABLE as 
(
    SELECT NAME, count(NAME) 
    FROM ANIMAL_INS
    WHERE NAME IS NOT NULL
    GROUP BY NAME
    ORDER BY NAME
)

SELECT * FROM TEMP_TABLE
WHERE COUNT > 1

 

with절은 동일한 SQL이 반복되어서 사용될 때 성능을 높이기 위해 사용된다.

table을 만들지 않고도 table 만든 것과 같은 효과를 내는데, 실제로는 temp라는 임시 테이블에 저장되는 것이다.

​

비슷한 기능을 하는 뷰는, 쿼리 결과문을 테이블로 저장하는게 아니라, 그냥 별칭만 해주는 것이다.

그래서 뷰를 불러오면 쿼리문이 그대로 실행하는 것과 같다.

 

하지만 with는 가상의 테이블, 즉 테이블이다.

대신에 메모리를 차지한다는 단점이 있다.

# UNION 결과 : 1,2 출력

SELECT 1 AS num
UNION
SELECT 2 AS num;

# WITH, UNION 결과 : 1,2 출력

WITH test AS
(
  SELECT 1 AS num
  UNION ALL
  SELECT 2 AS num
)

SELECT * FROM test;

# WITH, UNION 결과 : 1,2,2,2,... (emp테이블의 행수만큼 2가 반복됨)

WITH test AS
(
  SELECT 1 AS num
  UNION ALL
  SELECT 2 AS num FROM emp # 이 문장은 emp 테이블의 행수만큼 반복된다
)

SELECT * FROM test;

---

​예시)

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

​

노멀 쿼리

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절의 장점은 temp라는 임시 테이블을 사용해서 장시간 걸리는 쿼리의 결과를 저장해놓고 저장해놓은 데이터를 엑세스하기 때문에 성능이 좋다.

​

그렇다면 모두 with절로 사용하면 되지 않는가? 라는 질문에 답하면 아니다.

with절을 너무 남발해서 같은 시간에 여러 개의 with절을 동시에 돌리면 temp(임시 테이블)가 견딜 수 있는 정도가 넘어가서 다같이 느려지기 때문이다.

 

이를 위해 with절에는 두 가지 옵션이 있다.

​

• materialize

materialize는 기본 with절 사용 시 적용되는 것으로 temp(임시 테이블)를 사용한다.

그래서 실행시간이 단축된다.

​

• inline

inline은 temp 테이블을 생성하지 않고 inline 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);

---

커스텀 임시 테이블

테이블을 만들때 아에 임시 테이블로 만드는 방법도 있다.

다만 "임시"라는 의미에 맞게 상 언제까지 저장되는 테이블이라는 조건이 있어야 하는데,

• ​commit 할 때 까지 : on commit delete rows
• 세션이 종료될 때까지 : on commit preserve rows (SQL을 끌때까지)

​으로 구분할 수 있다.

​

예제)

Q. 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; -- ← 데이터가 없다!

---

Reference

https://hsdevelopment.tistory.com/653

https://hyunmin1906.tistory.com/149

https://velog.io/@cyanred9/SQL-Recursive

https://superkong1.tistory.com/35