[DB/SQL] ORACLE WITH절 사용법 정리

 

 

 

오늘은 SQL 작성 시에 자주 사용되는 임시테이블 WITH절에 대해서 정리해 보겠습니다.

 

 

WITH 절이란?

WTTH 절은 오라클에서 정의된 문장으로 공유 메모리에 임시 테이블을 생성하여 반복 재사용이 가능하도록 할 수 있는 절이다.  메모리에 임시 테이블을 생성해서 동일 테이블 접근을 최소화하고 디스크 IO로 접근하는 것보다 성능을 개선할 수 있기 때문에 사용한다.

 

 왜 WITH절을 사용하는 것일까? 

WITH절은 복잡하고 반복적인 SQL문을 사용하는 경우 이름을 부여해서 재사용할 수 있게 해서 쿼리의 성능을 높일 수 있다. 이름을 부여한다는 점은 QUERY BLOCK을 만들어 자주 실행되는 경우 한 번만 파씽되고 플랜 계획도 수립되어 쿼리 성능 향상에 도움이 된다.

 

WITH TEST_WITH AS(
    SELECT 'WITH1' FROM DUAL
)
SELECT * FROM TEST_WITH

위 쿼리처럼 WITH절에 서브쿼리를 만들어서 사용할 수 있다.

 

 

예시)  직원 테이블에서 직책별로 나이가 어린 사람을 추출하는 쿼리

[테스트 데이터]

SELECT * FROM EMP_TABLE;

 

 

 

 

 

(WITH절을 사용 안 하는 경우)

SELECT 
        EMP_TABLE.* 
  FROM EMP_TABLE EMP_TABLE
      ,(
            SELECT 
                EMP_ID
                ,EMP_NAME
                ,GENDER
                ,AGE
                ,POSITION
                ,HIRE_DATE
                ,ETC
                ,RANK() OVER( PARTITION BY EMP_TABLE.POSITION ORDER BY EMP_TABLE.AGE ) RANK
          FROM EMP_TABLE
      )TARGET_EMP_TABLE
WHERE EMP_TABLE.EMP_ID = TARGET_EMP_TABLE.EMP_ID
  AND TARGET_EMP_TABLE.RANK = '1'

FROM절에 서브쿼리를 만들어서 JOIN으로 추출하였다.

 

 

(WITH절을 사용한 경우)

WITH TARGET_EMP_TABLE AS (
            SELECT 
                EMP_ID
                ,EMP_NAME
                ,GENDER
                ,AGE
                ,POSITION
                ,HIRE_DATE
                ,ETC
                ,RANK() OVER( PARTITION BY EMP_TABLE.POSITION ORDER BY EMP_TABLE.AGE ) AS RANK
          FROM EMP_TABLE
)
SELECT
       EMP_TABLE.*
  FROM EMP_TABLE EMP_TABLE
     , TARGET_EMP_TABLE
 WHERE EMP_TABLE.EMP_ID = TARGET_EMP_TABLE.EMP_ID
   AND TARGET_EMP_TABLE.RANK = '1'

 

 

 

WITH절로 임시테이블을 만들어 데이터 추출도 가능하다. 사실 서브쿼리 한 번만을 사용해서는 성능차이는 많지는 않지만, UNION ALL과 같이 여러 번 참조해서 쿼리를 만들어 사용하면 성능효과는 상승한다.

 

감사합니다.