[Oracle] 오라클 WITH 절 사용법 및 동작방식 (WITH 여러개)
- 데이터베이스/오라클
- 2021. 6. 28.
오라클 9i R2부터 WITH 절을 사용할 수 있도록 기능이 추가되었다. WITH 절은 임시 테이블 또는 가상 테이블이라고 생각하면 된다. 반복되는 서브쿼리 블록을 하나의 WITH 절 블록으로 만들어서 사용하거나, 긴 문장의 서브 쿼리를 WITH 절로 만들어서 SELECT 문의 가독성을 높일 수 있다. WITH AS를 여러 개 사용하기 위해서는 쉼표를 붙여서 여러 개 선언하면 된다.
오라클 12c부터 WITH 절에 함수와 프로시저를 선언할 수 있으니 아래를 참고하면 된다.
WITH 절 사용법
WITH emp_w1 AS
(
SELECT deptno
, SUM(sal) AS sal
FROM emp
GROUP BY deptno
)
SELECT a.deptno
, b.dname
, a.sal
FROM emp_w1 a
, dept b
WHERE a.deptno = b.deptno
서브쿼리를 WITH 절로 만들어서 테이블을 사용하듯 사용할 수 있다. 특히 여러 번 반복되는 서브쿼리를 WITH 절로 만들어서 사용하면 쿼리의 성능을 높일 수 있다.
오라클 11g R1까지는 WITH 절에 선언된 임시 테이블을 SELECT 문에 사용하지 않으면 오류가 발생하였지만, 11g R2부터는 선언 후 사용하지 않아도 오류가 발생하지 않는다.
다중 WITH 절 사용법 (여러 개)
WITH emp_w1 AS
(
SELECT deptno
, AVG(sal) AS sal_avg
FROM emp
GROUP BY deptno
),
emp_w2 AS
(
SELECT job
, AVG(sal) AS sal_avg
FROM emp
GROUP BY job
)
SELECT a.ename
, a.job
, a.sal
, b.sal_avg AS dept_avg
, c.sal_avg AS job_avg
FROM emp a
, emp_w1 b
, emp_w2 c
WHERE a.empno = 7788
AND a.deptno = b.deptno
AND a.job = c.job
WITH 절에 여러 개의 임시 테이블을 만들고 싶을 때는 쉼표(", ")를 찍고 이어서 선언하면 된다.
WITH 절 함수, 프로시저 선언 방법
WITH
FUNCTION fn_get_dept_name(p_deptno IN NUMBER)
RETURN VARCHAR2
IS
v_dname VARCHAR2(14);
BEGIN
SELECT dname
INTO v_dname
FROM dept
WHERE deptno = p_deptno;
RETURN v_dname;
END;
SELECT ename
, deptno
, fn_get_dept_name(deptno) AS dname
FROM emp
WHERE empno = 7788
오라클 12c부터 WITH 절에 함수를 선언할 수 있으며, 선언된 함수는 SELECT 문에서 사용할 수 있다.
WITH
PROCEDURE with_procedure(p_dname IN VARCHAR) IS
BEGIN
DBMS_OUTPUT.put_line('p_dname=' || p_dname);
END;
FUNCTION fn_get_dept_name(p_deptno IN NUMBER)
RETURN VARCHAR2
IS
v_dname VARCHAR2(100);
BEGIN
SELECT dname
INTO v_dname
FROM dept
WHERE deptno = p_deptno;
-- with 절에 선언된 프로시저 호출 가능
with_procedure(v_dname);
RETURN v_dname;
END;
SELECT ename
, deptno
, fn_get_dept_name(deptno) AS dname
FROM emp
WHERE empno = 7788
프로시저도 선언이 가능하며 프로시저는 SELECT 문에는 사용할 수 없으며, WITH 절에 선언된 함수 내부에서만 사용이 가능하다.
WITH 절 동작 방식 (힌트 사용)
오라클의 WITH 절은 Materialize과 Inline View 2가지의 동작 방식이 있다.
Materialize 방식 : 임시 테이블을 생성 후 WITH 절의 결과를 저장하며, 반복 호출 시 쿼리를 실행하지 않고 임시 테이블에 저장된 결과를 사용한다. Inline 방식 : 임시 테이블을 생성하지 않고 Inline View 형태로 수행하며, 참조된 횟수만큼 반복적으로 쿼리를 실행한다. |
오라클 11g 이전에는 옵티마이저가 동작 방식을 결정하였으며, 11g부터는 직접 힌트를 사용하여 동작 방식을 바꿀 수 있다. (옵티마이저 결정 방식: 2번 이상 수행되면 Materialize, 1회만 수행되면 Inline View)
Materialize 방식은 서브 쿼리의 조회 시간이 오래 걸리고 WITH 절의 임시 테이블을 여러 번 반복해서 사용한다면 성능을 높일 수 있는 방식이지만, 임시 테이블을 Create 하고 Drop 하는 행위를 반복하기 때문에 자주 수행되는 쿼리문에 사용하면 시스템에 부하가 생길 수 있다.
대부분 WITH 절을 사용할 때는 힌트를 사용하지 않고 옵티마이저 결정 방식으로 사용하지만, WITH 절의 동작 방식을 이해했다면 힌트를 사용하여 조금 더 효율 적으로 쿼리문을 작성할 수 있을 것이다.
Materialize 힌트
WITH emp_w1 AS
(
SELECT /*+ materialize */
deptno
, SUM(sal) AS sal
FROM emp
GROUP BY deptno
)
SELECT a.deptno
, b.dname
, a.sal
FROM emp_w1 a
, dept b
WHERE a.deptno = b.deptno
Inline 힌트
WITH emp_w1 AS
(
SELECT /*+ inline */
deptno
, SUM(sal) AS sal
FROM emp
GROUP BY deptno
)
SELECT a.deptno
, b.dname
, a.sal
FROM emp_w1 a
, dept b
WHERE a.deptno = b.deptno
동일한 WITH 절을 사용한 쿼리문 이지만 힌트에 따라서 쿼리문의 수행 방식이 달라지는 것을 확인할 수 있다.