[Oracle] 임시테이블 만들기 / WITH절

 

WITH절

WITH 절은 RDBMS계의 3대장인 Oracle, MySQL, MS-SQL 에서 기본적으로 지원된다.

오라클에서는 오라클9 이후 버전부터 사용이 가능하다.

WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만든다. 자주 실행되는 경우 한 번만 Pasing 되고 Plan 계획이 수립되므로 쿼리의 성능 향상에 도움이 된다.

 

WITH절은 이름이 부여된 서브쿼리라고 생각하면 된다. 임시테이블을 만든다는 관점에서 보자면 VIEW와 쓰임새는 비슷하지만 VIEW는 한번 만들면 DROP 할 때까지 없어지지않지만, WITH절은 한번 실행할 쿼리문내에 정의되어 있을 경우 그 쿼리문 안에서만 실행된다는 차이점이 있다.

 

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

 

 

 

WITH절을 사용해야 하는 이유

WITH절은 복잡한 SQL내에서 반복되는 동일한 구문을 재사용하기위해 사용한다.

SQL문을 작성하다보면 쿼리구문을 반복해서 작성해야 하는 경우가 발생하는데, 한 두번은 개발자가 작성하기 쉽지만 그 이상이 되면 작성하기 귀찮아지기 마련이다. 따라서, 개발자의 입장에서는 한 번 작성해놓고 재사용을 하는 쿼리문을 생각하게 되는데 이럴때 쓰라고 나온 것이 WITH절이라고 한다.

 

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

 

WITH절에 정의된 내용을 한번만 사용을 한다면 서브쿼리를 사용하는 것과 크게 성능차이가 나지는 않지만 한번 정의하면 반복적인 재사용이 가능하다는 점에서 큰 의미가 있다. 따라서 WITH 절에 구문을 여러번 참조하는 쿼리를 만들수록 효과가 크게 증가한다.

 

 

 

사용방법

>> 1개의 임시테이블 <<
WITH 임시테이블명  AS (
     SUB QUERY문 (SELECT절)
)
SELECT 컬럼, [컬럼,...]
FROM  임시테이블명


>> 2개 이상의 임시테이블  <<
WITH
임시테이블명1  AS (
     SUB QUERY문 (SELECT절)
),
임시테이블명1  AS (
     SUB QUERY문 (SELECT절)
)
SELECT  컬럼, [컬럼,...]
FROM  임시테이블명1, 임시테이블명2

 

 

 

예시

출처 : https://royzero.tistory.com/50  (자세한 설명은 해당 블로그 링크로)

 

다음과 같은 두 개의 테이블을 가정한다.

 

이 두 테이블을 JOIN 해서 네이버쇼핑에 대해서만 보고싶은 경우 다음과 같은 SQL문을 작성할 수 있다.

SELECT S.SITE_CODE, 
       S.SITE_NAME, 
       V.VNDR_CODE, 
       V.VNDR_NAME
  FROM TB_SITE AS S
  JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE

 

해당 쿼리문에 대한 INNER JOIN으로 다음과 같은 결과가 나온다.
(LEFT OUTER JOIN, RIGHT OUTER JOIN이 아닌 그냥 JOIN을 사용한 경우에는 INNER JOIN이다.)

 

 

나온 결과에서 '네이버 쇼핑'만 추출해서 사용하고 싶을때 다음과 같은 조건을 걸어 줄 수 있다.

네이버 쇼핑 >>  SITE_CODE = '101'

SELECT S.SITE_CODE, 
       S.SITE_NAME, 
       V.VNDR_CODE, 
       V.VNDR_NAME
  FROM TB_SITE AS S
  JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
 WHERE S.SITE_CODE = '101'

 

 

 

여기서 만약, 이렇게 작성한 하나의 테이블 이외에

'G마켓' 데이터만을 추출하는 SQL 쿼리문을 만들어서 서로 이어붙이고 싶다면 어떻게 해야할까?

아래와 같이 UNION ALL을 사용하여 작성할 수 있다.

(물론 조건만 조정하면 한 개의 SQL문으로 작성가능하지만 WITH 절을 사용해 보기 위해 UNION ALL을 사용하였다.)

SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
  FROM TB_SITE AS S
  JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
 WHERE S.SITE_CODE = '101'
 UNION ALL
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
  FROM TB_SITE AS S
  JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
 WHERE S.SITE_CODE = '102'

 

이렇게 나온 '네이버쇼핑'과 'G마켓'이 합쳐진 테이블에서 업체가 '애플'인 데이터만 추출하기 위해서는 어떻게 해야할까? 아래와 같이 나타낼 수 있다.

SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
  FROM TB_SITE AS S
  JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
 WHERE S.SITE_CODE = '101'
   AND V.VNDR_CODE = '1001'
 UNION ALL
SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
  FROM TB_SITE AS S
  JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
 WHERE S.SITE_CODE = '102'
   AND V.VNDR_CODE = '1001'

 

각각의 테이블에 AND V.VNDR_CODE = '1001' 을 추가하기만 하면 된다.

 

하지만 여기서는 고작 2번 추가만 해주면 되지만 만약 이렇게 추가해서 수정해야할 SQL 쿼리문의 개수가 너무 많다면 유지보수 반면에서도, 데이터의 정합성에서도 올바른 방법은 아니다.

따라서 이때 WITH절을 사용해 주는 것이다.

 

WITH절을 이용하여 위의 코드를 수정하면 다음과 같다.

WITH VW_VENDOR AS (
    SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
      FROM TB_SITE AS S
      JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
     WHERE S.SITE_CODE = '101'
     UNION ALL
    SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
      FROM TB_SITE AS S
      JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
     WHERE S.SITE_CODE = '102'
)
SELECT *
  FROM VW_VENDOR
 WHERE VNDR_CODE = '1001'

 

 

만약 위의 코드를 서브쿼리로 작성했다고 한다면 아래와 같다.

SELECT V.*
  FROM (SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
          FROM TB_SITE AS S
          JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
         WHERE S.SITE_CODE = '101'
         UNION ALL
        SELECT S.SITE_CODE, S.SITE_NAME, V.VNDR_CODE, V.VNDR_NAME
          FROM TB_SITE AS S
          JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
         WHERE S.SITE_CODE = '102'
       ) V
 WHERE V.VNDR_CODE = '1001'

 

서브쿼리로 사용한것과 WITH절을 이용해 작성한 쿼리문은 차이점이 존재한다.

서브쿼리를 이용해 작성한 쿼리문은 그 한번으로 사용이 끝나지만,

WITH절을 사용한 임시 테이블은 해당 SQL 내에서는 언제든 재사용이 가능하기 때문에 개발자입장에서는 편리하다.

 

하지만 주의할 점은 WITH절이나 서브쿼리는 VIEW라는 임시 저장을 만드는 행위이기 때문에 SQL문 작성에 그리 좋은 습관은 아니라는 것이다. 오히려 위에서 주어진 조건을 SQL문에서는 아래와 같이 쿼리문을 작성하는 것이 RDBMS 자원 사용 입장에서는 더 효율적이다. 따라서 쿼리를 효율적으로 사용하기 위해 많은 고민이 필요하다고 볼 수 있다.

SELECT S.SITE_CODE, S.SITE_NAME, SV.VNDR_CODE, SV.VNDR_NAME
  FROM TB_SITE AS S
  JOIN TB_SITE_VENDOR AS SV ON S.SITE_CODE = SV.SITE_CODE
 WHERE S.SITE_CODE IN ('101', '102')
   AND SV.VNDR_CODE = '1001'

 

 

 

 

---

참고

https://royzero.tistory.com/50

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

https://grandma-coding.tistory.com/entry/OracleWITH%EC%A0%88