SQL) WITH절을 이용해 가독성 높이기

WITH절

WITH절을 이용해 추출한 데이터들을 서브 쿼리, 임시 테이블과 같이 사용할 수 있습니다. 해당 SQL문 내에서는 재활용이 가능하기 때문에, 중복되는 코드에서는 WITH절을 이용하여 가독성을 향상시킬 수 있습니다. 대표적인 RDBMS(Oracle, MySQL, MS-SQL 등)에서 사용 가능한 문법입니다.

어떨 때 사용해야 할까?

WITH절은 가독성을 좋게할 뿐만 아니라 경우에 따라 쿼리 성능을 향상시킬 수도 있습니다. 어떨 때 쿼리 성능이 향상될 수 있는지를 알기 위해서는 먼저 WITH절의 동작 방식을 알아야 합니다.

WITH절은 두 가지의 동작 방식이 존재합니다. 상황에 따라서 Inline View 방식과 Materialize 방식 중 한 가지 방식으로 동작하게 됩니다. 오라클을 예로, 이전 버전에서는 1회 호출 시 쿼리문 그 자체로 저장되어 호출되는 만큼 실행된다고 합니다. 이럴 경우엔 단순한 서브 쿼리와 동일하게 동작합니다. 반면에 2회 이상 호출시 Materialize 방식으로 동작하게 되는데, 이 때는 메모리에 임시 테이블을 생성(create)해 데이터를 저장한 후 SQL문이 끝나면 이 테이블을 삭제(drop)합니다.

결론

1. WITH절을 이용해 임시 테이블을 만들어서 쿼리 성능을 향상시키려면 한번 호출될 때 만들기보다는 여러 번 호출될 때 이를 이용하는 것이 좋아보입니다.
2. 여러 번 호출된다면 Materialize 방식으로 동작하게 되는데, 이럴 경우 조회해온 레코드의 수가 너무 많으면 내부 메모리에 저장하는데에 부하가 생길 것입니다. 따라서, 소량의 레코드가 조회되어야 할 때가 사용하기에 적합해보입니다.

Cf. 오라클 11g 이전과 이후의 WITH절 동작 방식

오라클을 기준으로 Oracle 11g 이전 버전에서는 자동으로 옵티마이저가 그 동작 방식을 결정했습니다. (1회 호출시 Inline View 방식, 2회 이상 호출시 Materialize 방식) 11g 부터는 ₩_WITH_SUBQUERY 파라미터로 결정할 수 있습니다.

Cf. Materialize 방식

Global Temporary Table을 생성한 후 WITH절의 결과 레코드들을 여기에 저장해둔다. WITH절로 정의한 테이블이 호출될 때마다 Global Temporary Table에서 데이터를 조회해온다.

with wt1 as 
        (select /*+ materialize */
                *
        from   t1
        where  c2 in ('A','B','C') )
     , wt2 as 
        (select /*+ INLINE ₩*/
          *
         from   t2
         where  c2 in ('A','B','C')
         and    c3 <= 10 )
select wt1.*, wt2.*
from   wt1, wt2
where  wt1.c1 = wt2.c1
and    wt1.c2 = 'A';

사용 방법

-- 단일 임시 테이블 생성
WITH 테이블명 AS (
    서브 쿼리문(SELECT)   
)

SELECT * FROM 테이블명;


-- 여러 개의 임시 테이블 생성
WITH 테이블명1 AS (
    서브 쿼리문1
),
테이블명2 AS (
    서브 쿼리문2
),
테이블명3 AS (
    서브 쿼리문3
)

---

참고

http://www.gurubee.net/lecture/3787