[Oracle]WITH절 사용법 및 예제(임시테이블)

WITH절 이란?

 

WITH절은 다른 DBMS에서도 많이 지원하는 기능이나 오라클에서는 WITH절에 정의된 SQL 문장으로 오라클 공유 메모리에 임시 테이블을 생성하여 반복 재사용이 가능하도록 할 수 있다. 그렇게 하면 동일 테이블 접근을 최소화하며 메모리에 생성된 임시 테이블에서 필요한 데이터를 메모리로 접근하기 때문에 디스크 IO로 테이블에 접근하는 것보다 성능을 개선할 수 있다.

WITH절을 완전히 숙지하려면 UNION ALL, DECODE/CASE, WITH, ROLLUP/Grouping Sets 등의 사용법을 알아야 한다. 왜냐하면 위의 문장들은 서로 형태 변환이 가능하여 성능상 매우 유리하게 사용될 수 있기 때문이다.

 

WITH절을 사용하는 이유?

 

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

 

WITH절 사용법

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

위 쿼리는 WITH절을 이용한 아주 간단한 예제이다.

 

서브쿼리를 WITH 절로 만들어서 테이블을 사용하듯 사용할 수 있다.

특히 여러 번 반복되는 서브쿼리를 WITH 절로 만들어서 사용하면 쿼리의 성능을 높일 수 있다.

 

출처: 젠트의 프로그래밍 세상

 

 

 

오라클 11g R1까지는 WITH 절에 선언된 임시 테이블을 SELECT 문에 사용하지 않으면 오류가 발생하였지만,

11g R2부터는 선언 후 사용하지 않아도 오류가 발생하지 않는다.

 

 

 

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절에 구문을 여러 번 참조하는 쿼리를 만들수록 그 효과가 배로 증가한다.