WITH문

개요

회사에서 코드를 분석하다가 WITH문을 활용하여 SQL을 작성하는 케이스를 보았다. SubQuery를 재사용할 수 있다 정도만 어렴풋이 아는 상태여서, 정확하게 언제 활용해야하는지에 대해 알고 싶어 정리해보았다.

설명

WITH문이란?

WITH {테이블명} AS (
	-- WITH 절로 저장하고 싶은 SQL 쿼리문
)

어떤 기능인가

SubQuery를 정의하고 재사용할 수 있게 해주어, 임시 테이블처럼 활용할 수 있게 만든 기능이다.

임시 테이블을 만든다는 관점에서 VIEW와 비슷하지만, VIEW는 한 번 만들어놓으면 DROP할 때까지 없어지지 않는 반면, WITH 문은 한 번 실행할 쿼리문 내에서만 재사용 가능하다는 차이점이 있다.

언제 사용하는가

• 동일한 서브쿼리가 여러 번 반복될 때
• 복잡한 쿼리를 읽기 쉽도록 나눌 때
• 계층형 데이터를 처리할 때
  • 오라클 11g부터는 CONNECT BY 절로 계층형 쿼리를 구현할 수 있다.
• 일시적으로 계산 결과를 유지하고 싶을 때

왜 사용하는가

코드의 가독성을 높여준다.

각 서브쿼리가 여러 번 사용되면, 쿼리가 길어지고 가독성이 떨어지게 되는데, 이는 각 서브쿼리가 어떤 목적으로, 어떤 의미로 사용되었는지 알기 어렵다는 문제점이 있다.

→ 각 부분에 의미 있는 이름을 붙여 단계별로 표현한다면, 가독성 문제를 해소할 수 있다.

SQL의 성능을 개선시킨다.

복잡한 SQL문 내에서 반복적으로 사용하는 블록에 대해 이름을 부여하여 재사용할 수 있도록 함으로써 쿼리 성능을 높일 수 있다. 자주 실행되는 경우, 한 번만 파싱되고 실행 계획이 수립되므로 쿼리 성능 향상에 도움이 된다. (Materialize 방식인 경우)

예제

동물 병원에서 방문 기록을 관리하는 테이블이 있다고 가정하자.

각 동물의 최근 방문 날짜와 총 서비스 비용을 조회하는 쿼리를 작성해보자.

WITH문을 사용하지 않은 예제

SELECT
	lv.pet_id,
	lv.last_visit_date,
	(SELECT SUM(service_cost)
		FROM pet_clinic_visits
		WHERE pet_id = lv.pet_id) AS total_cost
FROM (SELECT pet_id, MAX(visit_date) AS last_visit_date
		FROM pet_clinic_visits
		GROUP BY pet_id) lv;

WITH문을 사용한 예제

WITH latest_visits AS (
	SELECT pet_id, MAX(visit_date) AS last_visit_date
	FROM pet_clinic_visits
	GROUP BY pet_id
),
total_service_costs AS (
	SELECT pet_id, SUM(service_cost) AS total_cost
	FROm pet_clinic_visits
	GROUP BY pet_id
)

SELECT lv.pet_id, lv.last_visit_date, tsc.total_cost
FROM lastest_visists lv
	JOIN total_service_costs tsc ON lv.pet_id = tsc.pet_id;

동작 방식

1) Inline View 방식

• WITH 문에서 추출한 결과 Set을 SQL에서 1회 사용될 경우, Global Temporary Table에 쿼리 결과를 저장하지 않고, 쿼리 그 자체로 저장해두는 방식이다.
• 따라서 WITH 문으로 정의된 테이블이 참조된 횟수만큼 반복 수행된다.
• 즉 SubQuery와 다를 것 없는 성능을 보여준다. (단, 가독성은 좋아진다.)

2) Materialize 방식

• 내부적으로 메모리에 Global Temporary Table을 생성하고, 실행 결과를 저장한 후, 반복해서 재사용하는 방식이다.
• Main SQL에서 WITH 문을 호출하면, 추출한 결과 Set이 저장되어 있는 Global Temporary Table을 읽어, 데이터를 처리한다.
• WITH문으로 정의된 테이블이 2번 이상 호출될 때 사용되며, 호출될 때마다 저장된 결과값을 불러온다.
• 즉 실행 횟수를 줄여줌으로써 개선된 성능을 보여준다.

Q) 이러한 방식은 누가 결정하는가?

SQL 엔진에 의해 결정되는데, 보통 쿼리 내에서 한 번 사용되거나 간단한 쿼리의 경우 Inline View 방식을 사용하고, 동일 쿼리가 여러 번 사용되거나 복잡한 계산을 포함할 때 Materialize 방식을 사용한다.

효율적으로 활용하는 방법

SQL 튜닝의 시작이라는 책에서 나온 내용으로 보인다.

1) 쿼리 실행 시 I/O 비용이 많이 들지만, 결과 row 수가 적은 경우 사용하기

쿼리 결과를 임시 테이블에 저장한 후, 저장된 테이블 값을 불러와 활용하는 materialize 방식을 사용한다고 가정했을 때, 여러 번의 Join을 통해 cost가 많이 드는 쿼리지만, 결과 row가 적은 경우 아주 큰 효율을 보여줄 수 있다. 한 번만 cost를 감당하면, 그 후로 결과값을 반복해서 사용할 수 있기 때문이다.

2) 결과 rows가 많은 경우에 materialize 방식은 비효율적일 수 있다.

WITH 문은 통해 임시 테이블을 create하고, drop하는 행위를 반복하기 때문에 시스템에 부하를 줄 수도 있다. 만약 WITH 문에서 사용된 쿼리의 결과 rows 수가 많다면, 해당 데이터들을 임시 테이블에 저장하는 과정에서 시스템에 부하를 줄 수도 있다고 한다.

참고

• 오라클 WITH절 사용법 & 예제
• with 절을 효율적으로 사용하기