[MySQL] WITH

설명

  • SQL에서 동일한 데이터를 반복처리시 성능개선 방법으로 사용
  • 데이터 건수는 적지만 데이터 추출시 I/O 처리량이 많은 경우 효과적
  • 여러 부분에서 사용될 데이터를 1회 추출하여 GLOBAL TEMPORARY TABLE에 저장하여 요청시 저장된 데이터만 읽어 처리하면 되므로 SQL성능 개선 및 I/O처리량이 대폭 감소
  • 비슷한 기능을 하는 뷰는 쿼리 결과문은 테이블로 저장하는게 아니라, 그냥 별칭만 해주는 것이다.
  • FROM 절의 서브쿼리(INLINE VIEW)도 마찬가지로 GLOBAL TEMPORARY TABLE을 사용한다.
  • WITH절은 GLOBAL TEMPORARY TABLE 을 사용하는 방식 외에 INLINE VIEW방식으로도 수행된다.
  • WITH절이 SQL에서 수행되는 회수가 1회면 INLINE VIEW방식을, 2회이상인 경우 MATERIALIZE방식을 사용한다.
  • WITH절 선언부분을 SQL의 가장 앞에 위치 시킨다. (데이터 정합성 문제 예방)
  • 통계쿼리나 튜닝할때, 많이 사용하고 쿼리량을 줄여주는 장점도 있다

단점

  • WITH절 사용시 MATERIALIZE 동작방식은 GLOBAL TEMPORARY TABLE에 WITH절의 SQL에서 추출한 데이터를 저장하고, 이 저장된 데이터를 읽어 처리해야 하기때문에 컨트롤 파일을 읽게된다. 그 과정에서 CONTROL FILE SEQUENTIAL READ라는 WAIT EVENT가 일어난다. 그러므로 동시성 높은 프로그램에서 WITH절을 MATERIALIZE 방식으로 사용하면 성능저하를 일으킬 위험이 있다.
  • 메모리를 차지 한다는 단점이 있다.

MATERIALIZE 방식

  • dafault
  • GLOBAL TEMPORARY TABLE를 사용

INLINE 방식

  • 임시 테이블은 생성하지 않고 INLINE VIEW로 수행하는 것
  • 실행방법은 with절 사용 시 select 뒤에 /+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);

사용

테이블

Contests table:
+------------+------------+--------------+--------------+
| contest_id | gold_medal | silver_medal | bronze_medal |
+------------+------------+--------------+--------------+
| 190        | 1          | 5            | 2            |
| 191        | 2          | 3            | 5            |
| 192        | 5          | 2            | 3            |
| 193        | 1          | 3            | 5            |
| 194        | 4          | 5            | 2            |
| 195        | 4          | 2            | 1            |
| 196        | 1          | 5            | 2            |
+------------+------------+--------------+--------------+

쿼리

WITH t1 AS (
    SELECT contest_id, gold_medal 
    FROM Contests 
), t2 AS (
    SELECT contest_id, silver_medal 
    FROM Contests
), t3 AS (
    SELECT contest_id, bronze_medal 
    FROM Contests
)
SELECT 
    t1.contest_id
    ,t2.silver_medal
    ,t3.bronze_medal
FROM t1
JOIN t2 ON t1.contest_id = t2.contest_id
JOIN t3 ON t2.contest_id = t3.contest_id
;

links

social