WITH절 이란?

WITH절은 오라클9 이후 버전부터 사용이 가능하며 이름이 부여된 서브쿼리라고 생각하면 된다. 임시테이블을 만든다는 관점에서 본다면 VIEW와 쓰임세가 비슷한데 차이점이 있다면 VIEW는 한번 만들어 놓으면 DROP할 때 까지 없어지지 않지만 WITH절은 한번 실행할 쿼리문내에 정의되어 있을 경우, 그 쿼리문 안에서만 실행된다는 차이점이 있습니다.

WITH절을 사용하는 이유

WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 그 블록에 이름을 부여하여 재사용할 수 있게 함으로서 쿼리 성능을 높일 수 있는데 WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있습니다. 자주 실행되는 경우 한번만 Parsing 되고 Plan 계획이 수립되므로 쿼리의 성능향상에 도움이 됩니다.

WITH EXAMPLE AS
(
	SELECT 'WITH절' AS STR1
	FROM DUAL
)

SELECT * FROM EXAMPLE

다중 WITH문

WITH EXAMPLE1 AS ( --첫번째 WITH문
SELECT 'EX1' A FROM DUAL 
UNION ALL
SELECT 'EX2' FROM DUAL
), 
EXAMPLE2 AS ( --두번째 WITH문
SELECT 'EX3' A FROM DUAL 
UNION ALL
SELECT A FROM EXAMPLE1 --첫번째 WITH문 참조
)
 
SELECT * FROM EXAMPLE2

위와 같이 한 쿼리문안에 WITH절을 여러개 쓰는 것도 가능하며, WITH절 안에서 다른 WITH절을 참조하는 것도 가능하다.

WITH절에 정의된 쿼리는 여러 번 사용할수록 효율이 증가합니다.

WITH EX AS (
SELECT *
FROM PRODUCTS 
WHERE STANDARD_COST > 1000
)
 
SELECT * FROM EX WHERE EX.CATEGORY_ID = '1'
UNION ALL
SELECT * FROM EX WHERE EX.CATEGORY_ID = '2'
UNION ALL
SELECT * FROM EX WHERE EX.CATEGORY_ID = '3'

WITH절에 정의된 내용을 한번만 사용한다면 서브쿼리를 사용하는 것과 크게 성능 차이가 나지 않습니다. WITH문의 가장 큰 장점은 한번 WITH절의 내용을 한번에 올려놓고 계속 재사용 한다는 것에 큰 의미가 있기에 WITH 절에 구문을 여러 번 참조하는 쿼리를 만들수록 그 효과가 배로 증가합니다.

사용 예

UPDATE mytable t		-- Update using WITH statement
   SET value3 = 
   (
      WITH comp AS 
      (
         SELECT id, value1
           FROM mytable t
          WHERE value2 > 10
      )
      SELECT c.value1
        FROM comp c
       WHERE c.id = t.id
   );