WITH절

 

WITH절 설명 & 사용법

정의

WITH절은 오라클9 버전부터 사용되었고, 이름이 부여된 임시 서브쿼리라고 생각하면 된다.

임시 테이블을 만든다는 관점에서 보면 VIEW 와 쓰임새가 비슷하다. 그러나 차이가 있다면 VIEW 는 DROP 되지 않는 한 재사용이 가능한 Object 이지만, WITH절의 경우 실행되는 쿼리문 안에서만 재사용된다.

 

사용법 예시

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

 

- 서브쿼리를 AS 뒤에 괄호에 넣고, AS 앞에 서브쿼리에 대한 이름을 붙여준다.

- WITH 절로 생성된 서브쿼리는 table 처럼 메인 쿼리에서 사용하면 된다.

 

 

WITH절 동작방식(Materialize, Inline)

Materialize 동작방식

1) Global Temporary Table 생성한 후, 결과 셋을 여기에 저장한다.

2) Main SQL 에서 with절을 호출하면, 추출한 결과 셋이 저장되어 있는 Global Temporary Table 을 읽어 데이터를 처리한다.

Materialize 제어 힌트 : /*+ MATERIALIZE */

 

Inline view 동작방식

Inline view 동작방식은 with절에서 추출한 결과 셋을 SQL 에서 1회 사용될 경우 Global Temporary Table 을 사용하지 않고, Inline View 형태로 수행되는 방식이다.

Inline View 제어 힌트 : /*+ INLINE */

 

 

SQL 성능 개선을 위한 WITH절 활용하기

데이터 중복 액세스 제거하기

SQL 결과 건수는 적으나, I/O 처리량이 많을 때 WITH절을 사용해서 데이터 중복 액세스를 제거할 수 있다.

SQL 에서 여러 부분에 사용될 데이터를 1회 추출하고(→ I/O 처리량 감소), 이를 Global Temporary Table 에 저장한다. 동일한 데이터에 대한 요청시 저장된 데이터만 읽어 처리하면 되기 때문에 SQL 성능을 개선할 수 있다. 

 

!주의!

WITH절로 추출된 데이터 건수가 많은 경우 global temporary table 에 저장하는 비용과 저장된 데이터를 읽을 때의 비용도 만만치 않기 때문에 꼭 WITH절을 써야 하는지 고민해봐야 한다.

 

VIEW PREDICATING* 성능 문제 제거하기

SQL 의 성능 문제 중 뷰 외부 조건이 뷰 내부로 침투되지 못하고, 부의 데이터를 모두 처리한 이후, 조인 연결 조건을 Filter 조건으로 사용하여 심각한 비효율이 발생하는 경우가 있다. 이 때, WITH절을 사용하여 성능 문제를 제거할 수 있다.

*VIEW PREDICATING : Optimizer 는 SQL 의 성능을 개선하기 위해 뷰 외부의 조건을 뷰 내부로 침투시키려고 시도하는데, 이때 뷰 내부로 조건을 성공적으로 침투시키게 되면, View predicating 이 발생되었다고 한다.

 

계층 쿼리의 데이터 처리 최소화 하기

계층 구조를 처리하는 SQL은 보통 START WIHT 절 → CONNECT BY절 → WHERE절 순서로 수행이 되며, 계층 구조를 처리하는 각 부분에 적절한 인덱스만 존재하면 성능 문제는 발생하지 않는다.

그런데, 계층 쿼리에 서브쿼리가 사용이 되었고, 서브쿼리가 먼저 실행이 되어야 성능이 더 좋으나 서브쿼리가 먼저 수행되지 않고 계층 구조 분석 이후에 서브쿼리 데이터를 처리할 때가 있다.

이때 서브쿼리에 MATERIALIZE 힌트를 포함하는 WITH절로 뺀 후에, SQL 을 수정하면 된다.

 

✔ WITH절 사용시 주의해야 할 점은?

동시성이 높은 경우 MATERIALIZE 동작방식은 피하자

WITH절이 Materialize 동작방식으로 수행되는 SQL을 모니터링 해보면, control file sequential read 라는 Oracle Wait Event 가 빈번하게 발생하는 것을 볼 수 있다. 이 이벤트는 DB서버의 컨트롤 파일을 읽을 때 발생하는 wait event 이다. WITH절을 처리하는 과정에서 Materialize 동작방식은 Global Temporary Table 에 WITH절의 SQL에서 추출한 데이터를 저장하고, 이 저장된 데이터를 읽어 처리해야 하기 때문에 컨트롤 파일을 읽게 된다. 

그러므로 동시성이 높은 프로그램에 성능을 개선할 목적으로 WITH 절을 사용하고 Materialize 동작방식을 적용한다면, 오히려 control file sequential read 대기 이벤트를 대기하는 세션이 급증하여 오리혀 DB 서버 성능에 악역향을 미칠 수 있으므로 주의해야 한다.

 

추출건수가 많은 경우 WITH절은 피하자

WITH절에 명시한 SQL 의 추출 건수가 많고, Materialize 동작방식으로 사용이 된 경우,

WITH절에서 추출한 많은 데이터를 Global Temporary table 에 저장해야 하고, 또한 WITH절의 데이터를 읽어야 할 때도 많은 데이터를 처리해야 하므로, WITH절을 사용해도 성능이 크게 개선되지 않을 수 있다.

 

따라서, WITH절은 동일한 데이터를 반복 조회 했을 때 1회 처리하는데 많은 시간이 소요되지만, 추출 건수가 몇 건 안되어 Global Temporary table 에 대한 쓰기/읽기 작업의 일량이 적은 경우에 한해서 사용하는 것이 바람직하다.

 

WITH절 선언 부분은 SQL의 가장 앞에 위치시키자

WITH 절이 SQL 중간 부에 위치할 경우 데이터 정합성 문제를 야기할 수 있다. 따라서 WITH절 선언부를 반드시 SQL의 맨앞에 명시해야 한다.

 

WITH절에 동작방식 힌트를 추가하자

SQL 의 구조가 변경되더라도, 실제 수행되는 동작방식은 기존과 동일하거나 성능개선의 목적과 부합하지 않는 동작방식으로 수행될 수 있기 때문에 WITH절에 동작방식 힌트를 추가하는 것이 좋다.

 

이 글은 SQL튜닝의 시작 서적의 내용을 참고하여 정리하였습니다.