[Oracle] 오라클(Oracle) WITH 절 사용하는 방법 및 예제

Oracle 로고

이번 글에서는 오라클(Oracle)에서 서브쿼리 또는 임시 테이블로 사용을 할 수 있도록 해주는 WITH절에 대해 글을 기록한다.

---

WITH 절

WITH은 오라클(Oracle) 9 이후의 버전에서 사용이 가능하다. WITH절은 위에서 말한 것과 같이, 서브쿼리 또는 임시 테이블로 사용을 할 수 있도록 해주는 기능을 가지고 있다.

 

WITH 절 VS 서브쿼리, VIEW

우리가 흔히 서브쿼리나 VIEW를 많이 사용한다.

 

서브쿼리의 경우, 한 개의 쿼리에서 우리의 입맛에 맞게 데이터를 뽑아내거나 할 때 사용을 하는데, 이 서브쿼리가 다른 쿼리에서도 공통적으로 사용을 하는 경우에 WITH절을 사용하면 된다.

 

VIEW 같은 경우에도 우리가 기존에 있던 쿼리나 임시 테이블 등을 만들어 쿼리를 짜 놓으면 그 쿼리에 맞게 데이터를 가지는 가상의 테이블이 된다.

 

그것처럼, WITH절도 VIEW나 서브쿼리처럼 사용 용도가 거의 비슷하다고 보면 된다.

 

WITH 절을 사용하는 이유

우리는 코드를 짤 때, 공통되는 변수나 서비스, 비즈니스 로직 등이 있으면 공통으로 묶어서 사용한다. 쿼리도 똑같다. 공통된게 있으면 하나로 묶어서 사용하는 것이 더 보기에도 좋고, 관리에도 더 좋은 방법이기도 하다. 물론, 때에 따라 다른 경우도 있으니 무조건적인 것은 아니다.

 

그래서, WITH 절을 사용하는 이유는 위와 같이 동일한 쿼리들을 공통적으로 묶어 공통적인 쿼리 문을 선언하여 사용하는 것이다. 이렇게 사용을 하면, 관리에도 편리하고 가장 큰 것은 쿼리에 대한 성능을 향상시킬 수 있다는 것이다. 3000만 개의 데이터가 있는 테이블을 여러 번 조회해서 가져오는 경우와 1번만 조회해서 그것을 계속 사용하는 경우에 대한 속도 차이는 상당할 것이다. 그렇기 때문에, 쿼리에 대한 성능을 향상시킬 수 있다는 의미다.

 

위의 성능에 대해서는 아래에서 실행 계획에 대한 측정을 할 것이다.

 

WITH 절 사용법

WITH 테이블이름 AS
(
	쿼리
)

 

WITH 절을 사용하는 방법은 정말 간단하다. 서브쿼리를 사용하듯이 위에 WITH 테이블 이름 AS (쿼리) 형식으로 사용하면 된다. 즉, 위의 쿼리와 같은 구조로 사용하면 된다.

 

아래에서 예시를 보자

 

컬럼이 NO, NAME, GRADE, SCORE가 있는 STUDENT 테이블과 GRADE와 NAME이 있는 GRADE 테이블이 있다. 여기서, 우리는 이 2개의 테이블을 GRADE 컬럼을 기준으로 INNER JOIN을 하여 나타내고 싶다.

STUDENT 테이블

GRADE 테이블

WITH GR AS
(
	SELECT GRADE
         , NAME
      FROM GRADE
)

    SELECT A.*
      FROM STUDENT S
INNER JOIN GRADE G ON S.GRADE = G.GRADE

 

이렇게 간결하게 사용할 수 있다. 물론, 위의 문제는 그저 테이블 JOIN을 하여 가져오면 되긴 하지만, GROUP BY 등을 통해 N번의 데이터 전처리를 하여 여러번 사용을 해야 하는 경우 위와 같이 WITH 절을 사용할 수 있다.

 

WITH 절의 경우 여러 개를 사용할 수도 있다.

WITH GR AS
(
	SELECT GRADE
         , NAME
      FROM GRADE
),
ST AS
(
	SELECT "TEST" AS NAME
      FROM DUAL
)

    SELECT A.*
      FROM STUDENT S
INNER JOIN GRADE G ON S.GRADE = G.GRADE

 

위와 같이, WITH 절을 선언한 아래에 콤마(,)를 사용한 다음에 선언하여 사용할 수 있다.

 

WITH 절의 동작 방식

WITH절의 동작 방식에는 MATERIALIZE 방식과 INLINE 방식으로 총 2가지가 있다.

 

1. MATERIALIZE 방식

MATERIALIZE 방식은 임시로 테이블을 생성하여 쿼리 결과 데이터를 글로벌 임시 테이블에 저장하고, 계속해서 반복 호출을 할 경우에는 WITH 절에 있는 쿼리를 반복 실행하지 않고 임시 테이블에 있는 데이터를 가져 간다.

 

2. INLINE 방식

INLINE 방식은 임시 테이블을 생성하지 않고, 호출된 횟수 N번 만큼 N번 실행하여 데이터를 가져 간다.

 

WITH 절의 MATERIALIZE, INLINE 방식은 힌트를 사용하여 컨트롤을 할 수가 있다.

WITH GR AS
(
	SELECT /+ MATERIALIZE */
    	   GRADE
         , NAME
      FROM GRADE
),
ST AS
(
	SELECT /+ INLINE */
           "TEST" AS NAME
      FROM DUAL
)

    SELECT A.*
      FROM STUDENT S
INNER JOIN GRADE G ON S.GRADE = G.GRADE

 

WITH 절 동작 방식의 실행계획

1. MATERIALIZE 방식

아래의 쿼리는 DUAL 테이블에 MATERIALIZE 힌트를 걸어 실행했다.

WITH TEST01 AS
(
	SELECT /*+ MATERIALIZE */
	       *
	  FROM DUAL
)

SELECT *
  FROM TEST01;

위의 MATERIALIZE 방식은 임시 테이블을 생성하는 것을 실행계획에서 볼 수 있다.

 

2. INLINE 방식

아래의 쿼리는 DUAL 테이블에 INLINE 힌트를 걸어 실행했다.

WITH TEST01 AS
(
	SELECT /*+ INLINE */
	       *
	  FROM DUAL
)

SELECT *
  FROM TEST01;

 

위와 같이, INLINE 방식에서는 MATERIALIZE 방식과 다르게 임시 테이블을 생성하지 않고 바로 조회를 한다.

 

요약

WITH절은 공통된 쿼리에 대한 것을 1개로 묶어서 사용하는 등의 간결함과 효율적인 관리, 쿼리 성능에 대한 향상을 기대할 수 있다. WITH 절을 사용할 경우에 각 DB에서의 옵티마이저가 최적인 것을 선택하여 실행을 하는데, 더 나아가 MATERIALIZE와 INLINE 힌트를 줘서 성능을 더 높일 수 있는 것에 대한 고민을 할 수도 있다.

---

참고

http://wiki.gurubee.net/pages/viewpage.action?pageId=27427116 

https://coding-factory.tistory.com/445

https://gent.tistory.com/419