[Oracle] 오라클 WITH절 사용법 & 예제 (임시 테이블 만들기)

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

 

WITH절이란

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

 

WITH절을 사용하는 이유

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

 

 WITH절 사용법 

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

SELECT * FROM EXAMPLE

최대한 간단하게 WITH절을 사용해봤습니다. WITH절의 안의 내용을 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절에 구문을 여러번 참조하는 쿼리를 만들수록 그 효과가 배로 증가합니다.