오라클 WITH 임시테이블

오라클 WITH

오라클 WITH란?

오라클에서는 서브쿼리를 임시테이블처럼 사용할 수 있는 WITH구문을 제공하고 있습니다. 통계 데이터를 만든다거나 집계정보를 만든다고 할 때 WITH구문은 아주 유용하게 사용할 수 있습니다. WITH구문을 사용하지 않고 그냥 서브쿼리로 처리해도 쿼리는 만들 수 있을지 모르겠으나 이 구문의 최대 장점은 재활용성에 있습니다. WITH를 선언해 하나의 가상테이블을 만들어 놓고 실제 테이블처럼 여러 번 호출해서 조회를 할 수 있습니다.

오라클 WITH 구문

CREATE TABLE 구문으로 테이블 생성하는 것과 비슷한 구문이지만 AS안에는 서브쿼리에 대한 정보가 들어가고 이 부분이 테이블화 되면서 가상테이블이름을 호출하면 실제 테이블처럼 사용하실 수 있습니다.

WITH 가상테이블명 AS (
   SELECT ...
     FROM ...
    WHERE ...
)
SELECT ...
  FROM 가상테이블명;

오라클 WITH 사용 방법

WITH절 선언만 하고 SELECT 쿼리를 작성하지 않으면 구문오류가 발생합니다. WITH절을 선언하면서 데이터를 한번 가공하고 그 내용을 토대로 또 데이터를 가공할 수 있습니다.

-- WITH예제( 고객별 마지막 로그인 이력정보를 가져오는 쿼리 입니다. )
WITH W_MAX_HIST AS (
    SELECT CUST_NO, MAX(LOGIN_DATE) AS LAST_LOGIN_DATE
      FROM CUSTOMER_HIST
     GROUP BY CUST_NO
)
SELECT *
  FROM CUSTOMER_HIST A
     , W_MAX_HIST B
 WHERE A.CUST_NO = B.CUST_NO
   AND A.LOGIN_DATE = B.LAST_LOGIN_DATE;

오라클 WITH 중첩 사용

WITH문을 적절하게 잘 사용하면 코드 재사용성을 높일 수 있을 뿐더러 가독성도 높일 수 있습니다. 하지만 실행 순서나 적절한 인덱스를 생각하지 않고 사용하게 되면 쿼리의 성능이 저하될 수 있습니다. 필요에 따라 적절히 쿼리 플랜을 보면서 잘 사용하면 아주 유용한 명령어가 아닐까 싶습니다.

-- 중첩 WITH사용
WITH W_USERS AS (
    SELECT '유리' AS USER_NAME, 22 USER_NO FROM DUAL UNION ALL
    SELECT '멘탈' AS USER_NAME, 33 USER_NO FROM DUAL UNION ALL
    SELECT '디노' AS USER_NAME, 11 USER_NO FROM DUAL
),
W_MAX_USERS AS (
    SELECT MAX(USER_NAME) AS USER_NAME
         , MIN(USER_NO) AS USER_NO
      FROM W_USERS
)
SELECT USER_NAME, USER_NO
  FROM W_MAX_USERS;
  

-- 결과
USER_NAME   USER_NO
----------------------
   유리       11