[ORACLE] 오라클 WITH 구문을 사용해 가상테이블 만드는 방법

오라클 WITH 구문을 사용해 가상 테이블 만드는 방법 타이틀

 

 

오라클 쿼리를 작성하다 보면 쿼리문 내에서 동일한 구문의 쿼리문이 반복해서 동일한 테이블에 엑세스하는 경우가 있습니다. WITH 구문은 그런 동일한 결과를 내는 반복 쿼리문을 줄여서 테이블 엑세스를 줄이고 쿼리문을 단순화하는데 유용합니다. WITH 구문을 이용하면 오라클 내에서 가상 테이블이 생성되어 메모리에 할당됩니다. 

 

오라클에서 WITH 구문을 사용하는 방법

WITH ..가상테이블명.. AS 구문을 사용하여 가상테이블을 구성할 데이터를 조회하는 쿼리를 만듦니다. 이 가상 테이블은 실제 테이블처럼 FROM 구문을 이용하여 외부 쿼리문 내에서 호출하여 사용할 수 있습니다.

WITH TEMP_TABLE AS (
 ... QUERIES...
)
SELECT COLUMNS FROM TEMP_TABLE

 

예제

WITH 가상 테이블은 아래와 같이 임의의 데이터들을 생성하고 테이블 명으로 외부 쿼리에서 실행할 수 있습니다. 안에 들어가는 쿼리문은 SELECT로 조회되는 어떤 쿼리라도 가능합니다.

WITH TEST_DATA AS (
    SELECT '김아무개' AS "이름",'과장' AS "직급", 'M' AS "성별", 38 AS "나이", 4500000 AS "월급" FROM DUAL UNION ALL
    SELECT '박아무개','과장', 'F', 37, 4600000 FROM DUAL UNION ALL
    SELECT '이아무개','차장', 'F', 42, 5100000 FROM DUAL UNION ALL
    SELECT '유아무개','대리', 'M', 34, 3200000 FROM DUAL UNION ALL
    SELECT '최아무개','주임', 'M', 28, 2500000 FROM DUAL UNION ALL
    SELECT '박아무개','대리', 'M', 32, 2800000 FROM DUAL UNION ALL
    SELECT '이아무개','주임', 'F', 33, 2600000 FROM DUAL UNION ALL
    SELECT '김아무개','주임', 'F', 26, 2700000 FROM DUAL UNION ALL
    SELECT '박아무개','이사', 'M', 56, 5700000 FROM DUAL UNION ALL
    SELECT '최아무개','과장', 'M', 45, 4500000 FROM DUAL UNION ALL
    SELECT '임아무개','대리', 'M', 35, 3700000 FROM DUAL
)
   SELECT * FROM TEST_DATA;

 

위 쿼리문을 조회한 결과는 아래와 같습니다. 임시ALIAS 로 지정한 '이름', '직급', '성별', '나이', '월급' 컬럼들이 마치 실제 테이블처럼 결과로 조회됩니다.

 

WITH 예제 쿼리에 대한 결과문

 

WITH 쿼리를 다중으로 이용하는 방법

WITH 구문은 연속으로 다중 테이블로 구성할 수 있습니다. 다중으로 가상테이블을 생성하여 조인하거나 복합 쿼리문 내에서 활용할 수 있습니다. 

 

WITH TEST_TBL1 AS (
	... QUERY...
),
TEST_TBL2 AS (
	... QUERY...
),
TEST_TBL3 AS (
	... QUERY...
)
SELECT COLUMN1, COLUMN2, ...
  FROM TEST_TBL1 A, TEST_TBL2 B, TEST_TBL3 C
WHERE A.KEY = B.KEY
  AND A.KEY = C.KEY

 

위 쿼리처럼 동일 외부 쿼리에서 사용할 가상 테이블을 다중으로 생성하여 다양한 쿼리를 활용하여 테이블 데이터를 사용할 수 있습니다. 꼭 조인문이 아니더라도 스칼라 쿼리 등등 실제 테이블처럼 서브쿼리로 사용할 수 있어서 활용도가 높습니다. 무엇보다 상단에 쿼리문 안에서 복잡하게 조회하는 데이터들을 가상테이블로 정렬하여 한 눈에 볼 수 있어서 깔끔합니다.

 

예제

 

아래 쿼리문처럼 가상 테이블을 열거식으로 다중테이블로 구성할 수 있습니다. 가상테이블을 활용하는 쿼리문은 실제 테이블을 사용하는 것처럼 여러가지 방법으로 활용가능합니다. 여기서는 조인으로 사용했으나, 일부 서브쿼리로 사용한다든지, GROUP BY 등의 집계함수를 사용하는 등 다양하게 사용할 수 있습니다.

WITH TEST_NAME AS (
    SELECT 10021 AS USER_KEY, '김아무개' AS "이름" FROM DUAL UNION ALL
    SELECT 10022 AS USER_KEY, '박아무개' FROM DUAL UNION ALL
    SELECT 10023 AS USER_KEY, '이아무개' FROM DUAL UNION ALL
    SELECT 10024 AS USER_KEY, '유아무개' FROM DUAL UNION ALL
    SELECT 10025 AS USER_KEY, '최아무개' FROM DUAL UNION ALL
    SELECT 10026 AS USER_KEY, '박아무개' FROM DUAL UNION ALL
    SELECT 10027 AS USER_KEY, '이아무개' FROM DUAL UNION ALL
    SELECT 10028 AS USER_KEY, '김아무개' FROM DUAL UNION ALL
    SELECT 10029 AS USER_KEY, '박아무개' FROM DUAL UNION ALL
    SELECT 10030 AS USER_KEY, '최아무개' FROM DUAL UNION ALL
    SELECT 10031 AS USER_KEY, '임아무개' FROM DUAL
),
TEST_PLACE AS (
    SELECT 10021 AS USER_KEY, '과장' AS "직급" FROM DUAL UNION ALL
    SELECT 10022 AS USER_KEY, '과장' FROM DUAL UNION ALL
    SELECT 10023 AS USER_KEY, '차장' FROM DUAL UNION ALL
    SELECT 10024 AS USER_KEY, '대리' FROM DUAL UNION ALL
    SELECT 10025 AS USER_KEY, '주임' FROM DUAL UNION ALL
    SELECT 10026 AS USER_KEY, '대리' FROM DUAL UNION ALL
    SELECT 10027 AS USER_KEY, '주임' FROM DUAL UNION ALL
    SELECT 10028 AS USER_KEY, '주임' FROM DUAL UNION ALL
    SELECT 10029 AS USER_KEY, '이사' FROM DUAL UNION ALL
    SELECT 10030 AS USER_KEY, '과장' FROM DUAL UNION ALL
    SELECT 10031 AS USER_KEY, '대리' FROM DUAL
),
TEST_AGE AS (
    SELECT 10021 AS USER_KEY,  8 AS "나이" FROM DUAL UNION ALL
    SELECT 10022 AS USER_KEY,  37 FROM DUAL UNION ALL
    SELECT 10023 AS USER_KEY,  42 FROM DUAL UNION ALL
    SELECT 10024 AS USER_KEY,  34 FROM DUAL UNION ALL
    SELECT 10025 AS USER_KEY,  28 FROM DUAL UNION ALL
    SELECT 10026 AS USER_KEY,  32 FROM DUAL UNION ALL
    SELECT 10027 AS USER_KEY,  33 FROM DUAL UNION ALL
    SELECT 10028 AS USER_KEY,  26 FROM DUAL UNION ALL
    SELECT 10029 AS USER_KEY,  56 FROM DUAL UNION ALL
    SELECT 10030 AS USER_KEY,  45 FROM DUAL UNION ALL
    SELECT 10031 AS USER_KEY,  35 FROM DUAL
),
TEST_WAGE AS (
    SELECT 10021 AS USER_KEY, 4500000 AS "월급" FROM DUAL UNION ALL
    SELECT 10022 AS USER_KEY, 4600000 FROM DUAL UNION ALL
    SELECT 10023 AS USER_KEY, 5100000 FROM DUAL UNION ALL
    SELECT 10024 AS USER_KEY, 3200000 FROM DUAL UNION ALL
    SELECT 10025 AS USER_KEY, 2500000 FROM DUAL UNION ALL
    SELECT 10026 AS USER_KEY, 2800000 FROM DUAL UNION ALL
    SELECT 10027 AS USER_KEY, 2600000 FROM DUAL UNION ALL
    SELECT 10028 AS USER_KEY, 2700000 FROM DUAL UNION ALL
    SELECT 10029 AS USER_KEY, 5700000 FROM DUAL UNION ALL
    SELECT 10030 AS USER_KEY, 4500000 FROM DUAL UNION ALL
    SELECT 10031 AS USER_KEY, 3700000 FROM DUAL
)
   SELECT N.USER_KEY, "이름", "나이", "월급"
     FROM TEST_NAME N, TEST_AGE A, TEST_WAGE W
   WHERE N.USER_KEY = A.USER_KEY
     AND N.USER_KEY = W.USER_KEY;

 

위 쿼리문을 실행한 결과는 아래와 같습니다.

 

WITH 다중 쿼리 예제에 대한 결과문

 

마무리

 

오라클의 WITH 구문은 쿼리문의 가독성을 높이고 빈번하게 동일 SQL구문이 실행되면서 동일블럭에 대한 반복적인 접근이 발생하는 경우의 부하를 줄여준다는 장점이 있습니다.  하지만 다량의 데이터들이 가상테이블로 생성되면 메모리나 성능의 저하를 일으킬 수 있다는 단점이 있습니다. 가상테이블을 활용할때는 데이터 양이 적거나 DISK I/O가 많이 발생하는 쿼리에만 선택적으로 사용하는 것이 괜찮을듯 합니다.