[Oracle] WITH절 (가상 테이블) 예제 및 장단점

Oracle WITH 절 개요

Oracle의 With절은 Oracle 9i 버전부터 도입된 기능으로 서브쿼리에 이름을 지정하여 재사용할 수 있는 가상 테이블을 생성할 수 있습니다. WITH 절을 사용하면 복잡한 쿼리를 단순화하고, 상황에 따라 성능 향상에 도움을 줄 수 있습니다. (MySQL, MS-SQL에도 WITH 절이 있습니다.)

WITH절 사용 방법

-- WITH 절 한 개 사용

WITH 임시테이블명 AS 
(
	-- 쿼리
)

-- 메인 쿼리


-- WITH 절 여러 개 사용 (, 사용)
WITH TEMP_A AS 
(
	-- 쿼리
), 
TEMP_B AS
(
	-- 쿼리
)

-- 메인 쿼리

WITH 절 선언 후 테이블명 AS () 로 생성할 수 있습니다. 여러 개를 생성하고 싶은 경우 쉼표(,)를 사용합니다.

Oracle 11 R2 버전까지는 WITH 절에 SELECT 구문을 반드시 사용해야 합니다.
Orace 12c 버전부터는 WITH절에서 SELECT 구문 없이도 서브쿼리나 함수 등을 사용할 수 있게 되었습니다.

WITH dept_avg_sal AS (
  SELECT 
  	department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
)
SELECT 
	e.employee_id, e.first_name, e.last_name, d.department_name, e.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN dept_avg_sal s
ON e.department_id = s.department_id
WHERE e.salary > s.avg_sal;

다음과 같이 WITH 절 구문을 사용하여 쿼리를 작성할 수 있습니다. WITH절을 FROM절의 인라인뷰에 넣을 수 있지만 권장하지 않는다고 합니다. (밑에 정리)

With 절 동작 과정

Oracle의 WITH 절에서는 Materialize와 Inline View 두 가지 전략이 있습니다. 이 두 전략은 WITH 절에서 서브쿼리를 실행하는 방식을 결정합니다.

Materialize

Materialize는 WITH 절에서 정의된 서브쿼리 결과를 메모리에 저장하는 방식으로 GLOBAL TEMPORARY TABLE을 사용합니다. 즉, WITH 절에서 정의된 서브쿼리를 실행하고 결과를 임시 테이블에 저장한 후, 이를 메인 쿼리에서 사용하므로 여러 번 실행되는 경우 유리합니다.

Inline View

Inline View는 WITH 절에서 정의된 서브쿼리 결과를 물리적으로 생성하지 않고 메인 쿼리에서 바로 사용하는 방식입니다.
WITH 절에서 정의된 서브쿼리를 실행한 후, 이를 메인 쿼리에서 사용할 때마다 다시 실행합니다. 따라서 한 번만 실행하는 경우 유리합니다.

WITH dept_avg_sal AS (
  SELECT /*+ MATERIALIZE */
  	department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
)
SELECT 
	e.employee_id, e.first_name, e.last_name, d.department_name, e.salary
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN dept_avg_sal s
ON e.department_id = s.department_id
WHERE e.salary > s.avg_sal;

다음과 같이 힌트를 주어 동작 방식을 결정할 수 있습니다.

Oracle 11g 이전에는 옵티마이저가 동작방식을 결정 (2회이상 수행되면 Materialize, 1회만 수행되면 Inline View 방식)
이후에는 /*+ MATERIALIZE */ 또는 /*+ INLINE */ 힌트를 통해 제어가 가능합니다.

WITH 절 주의 사항

WITH절을 FROM절의 인라인뷰에 넣을 수 있지만 권장하지 않습니다. (11G 이전 버전 데이터 정합성 버그 존재)

Materialize 방식은 데이터 로우나 동시성이 많은 경우 사용하지 않고 조인 형태나 스칼라 서브쿼리를 사용하는 것이 바람직합니다.

임시테이블 관점에서 VIEW와 비슷하지만 트랜잭션이 종료되어도 계속 유지하는 VIEW와 달리 WITH절은 쿼리문 안에서만 실행됩니다.

REFERENCE

http://wiki.gurubee.net/pages/viewpage.action?pageId=27427116

https://engineering-skcc.github.io/sql/SQL_OracleWITH%EC%A0%88%EC%A0%95%EB%A6%AC/

저작자표시