[Oracle] 오라클 WITH절을 사용하여 임시테이블 만들기

최근 오라클9i R2부터는 WITH절을 사용하여 가상테이블을 사용할 수 있도록 했다. 일전에는 임시테이블을 별도로 생성해서 만들거나,  SELECT 문의 FROM 절에 반복되는 서브쿼리들을 생성하여 테이블을 불러오곤 했는데, 이제는 덕분에 가독성이 높아지게 된 것이다. 오늘은 가상테이블, 임시테이블을 생성하는 WITH절에 대해서 적어보려 한다. 

 

1. WITH절이란?

오라클 내 WTIH절은 SQL 문장에서 오라클 공유메모리에 임시로 테이블을 생성하여 반복재사용이 가능하도록 하는 구문이다. 동일 테이블 접근을 최소화하고 메모리에 생성된 임시테이블에서 필요한 데이터를 메모리로 접근하기 때문에 성능이 향상될 수 있다. 

 

2. WITH절의 사용방법

WITH절의 사용방법은 간단하다. WITH (TABLE 명) AS (SELECT ~~~FROM )을 사용하면 되는데 아래와 같이 사용하면 된다.

WITH TEMP1 AS
(
	SELECT COL1 AS NEW_COL
    FROM TABLE
)

SELECT NEW_COL 
FROM TEMP1

위와 같이 임시테이블명을 정해서 임시테이블에 들어갈 구문을 작성한 후, 추후에 생성된 임시테이블을 반복하여 사용하는 방식이다. 

이렇게 여러번 반복될 법한 서브쿼리를 WITH절로 만들어서 사용하면 쿼리의 성능을 높일 수도 있고 가독성도 좋아진다. 

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

주의할 점은 오라클 11g R1까지는 WITH절에 선언된 임시테이블을 SELECT문에 사용하지 않으면 오류가 발생했으나, 11g R2부터는 선언 후 사용하지 않아도 오류가 발생하진 않는다. 

 

3. 여러개의 WITH 절을 사용할 때

여러개의 WITH절을 사용할 때는 단순히 쉼표(,)를 찍고 이어서 선언하면 된다.

WITH TEMP1 AS
(
	SELECT COL1 AS NEW_COL1
    FROM TABLE1
),
WITH TEMP2 AS
(
	SELECT COL2 AS NEW_COL2
    FROM TABLE2
)

SELECT 
	NEW_COL1,
    NEW_COL2
FROM TEMP1 A,TEMP2 B
WHERE NEW_COL1 = NEW_COL2

 

4. WITH절을 사용할 때의 장점

WITH절에 정의된 내용을 한번만 사용한다면, 서브쿼리를 사용하는 것과 크게 성능 차이가 나지 않지만, WITH문의 가장 큰 장점은 한번 선언한 WITH절의 내용을 반복해서 사용할 수 있다는 것이다. WITH절을 여러번 사용할 수록 효과가 증가하고 가독성이 좋아지게 된다. 

 

이전 프로젝트를 진행할 때는 오라클 버전이 낮아 WITH절을 사용하지 못해 반복된 서브쿼리를 사용했지만, 이번에는 더욱 편리하고 가독성있게 SQL구문을 작성할 수 있게 되었다. 여러개의 서브쿼리를 연결하여 테이블을 생성해야 하는 사람들은 꼭 참고해보기를 바란다.

 

참고

https://gent.tistory.com/419

[Oracle] 오라클 WITH 절 사용법 및 동작방식 (임시 테이블)