Oracle WITH절 기본 사용법 및 응용 1
Updated:
예제 테이블 생성
샘플 테이블 생성
오라클 11G DBMS를 설치하면 샘플 유저인 SCOTT 유저가 설치되는데 EMP 와 DEPT라는 테이블을 기준으로 테스트해봅니다. DEPT 테이블의 부서별 위치를 가진 테이블이고 EMP 테이블의 부모테이블로 DEPTNO가 PK입니다. EMP는 고용인 테이블로서 DEPT의 자식테이블로 EMPNO가 PK이며, DEPTNO가 FK입니다. 아래의 예제를 가지고 알아봅니다.
- EMP테이블
| Empno | ename | job | mgr | hiredate | sal | comm | deptno |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980/12/17 00:00:00 | 800 | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 1981/02/20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981/02/22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981/04/02 00:00:00 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1981/09/28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981/05/01 00:00:00 | 2850 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 1981/06/09 00:00:00 | 2450 | 10 | |
| 7788 | SCOTT | ANALYST | 7566 | 1987/04/19 00:00:00 | 3000 | 20 | |
| 7839 | KING | PRESIDENT | 1981/11/17 00:00:00 | 5000 | 10 | ||
| 7844 | TURNER | SALESMAN | 7698 | 1981/09/08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987/05/23 00:00:00 | 1100 | 20 | |
| 7900 | JAMES | CLERK | 7698 | 1981/12/03 00:00:00 | 950 | 30 | |
| 7902 | FORD | ANALYST | 7566 | 1981/12/03 00:00:00 | 3000 | 20 | |
| 7934 | MILLER | CLERK | 7782 | 1982/01/23 00:00:00 | 1300 | 10 |
- DEPT테이블
| DEPTNO | DNAME | LOC |
|---|---|---|
| 10 | ACCOUNTING} | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
1. 오라클 WITH 절
오라클에서 사용하는 WITH절은 참 독특한 기능을 하나 가지고 있습니다. WITH절은 다른 DBMS에서도 많이 지원하는 기능이나 오라클에서는 WITH절에 정의된 SQL문장으로 오라클 공유메모리에 임시테이블을 생성하여 반복 재사용이 가능하도록 할수있습니다. 그렇게 하면 동일 테이블 접근을 최소화하며 메모리에 생성된 임시테이블에서 필요한 데이타를 메모리로 접근하기 때문에 디스크IO로 테이블에 접근하는것보다 성능을 개선할수 있습니다.
WITH절을 완전히 숙지할려면 UNION ALL, DECODE/CASE, WITH, ROLLUP/Grouping Sets 등의 사용법을 알아야 합니다. 왜냐하면 위의 문장들은 서로 형태변환이 가능하여 성능상 매우 유리하게 사용될수 있기 때문입니다. 이 방법은 2부에서 타루도록 하고, 본 칼럼에서는 기본적인 사용법외 SQL문장만 보고 동일 테이블을 반복 접근하는 UNION ALL 절을 WITH로 변환하여 동일테이블 반복접근을 최소화하여 성능을 개선하는 방법에 대해 알아보겠습니다.
2. 오라클 WITH 기본사용법
가. 기본 문법
WITH절은 인라인뷰나 서브쿼리, 스칼라쿼리등이 반복 재사용될때 동일 SQL 블록에 대해 한번만 WITH절로 지정해주고 그 블록에 이름을 부여하여 재사용 할 수 있게 함으로서 쿼리 성능을 높이거나, SQL의 복잡도를 간단하게 하고, 쿼리 LINE을 최소화하는대 사용하는 SQL문장입니다. 이때 지정한 WITH절을 SubQuery Factoring이라 부릅니다.
- 사용법 : 동일한 서브쿼리의 결과가 반복적으로 사용될때 비효율성을 방지하기위해
사용합니다
가. 단독사용시
- 원문장
SELECT *
FROM (SELECT 1 AS NO, '홍길동' AS NAME
FROM DUAL
UNION ALL
SELECT 2 AS NO, '이길동' AS NAME
FROM DUAL);
- WITH절 변경
WITH TMP AS
(SELECT 1 AS NO, '홍길동' AS NAME
FROM DUAL
UNION ALL
SELECT 2 AS NO, '이길동' AS NAME
FROM DUAL)
SELECT *
FROM TMP;
나. 복수 사용시
WITH TMP_A AS
(SELECT 1 AS NO, '홍길동' AS NAME
FROM DUAL UNION ALL
SELECT 2 AS NO, '이길동' AS NAME
FROM DUAL ),
TMP_B AS
(SELECT 1 AS NO, '010-8888-1234' AS TEL
FROM DUAL
UNION ALL
SELECT 2 AS NO, '010-1234-1234' AS TEL
FROM DUAL )
SELECT A.NO, A.NAME, B.TEL FROM TMP_A A, TMP_B B
WHERE A.NO = B.NO;
나. 사용예제
순서대로 따라해봅니다. 많이 사용하는 sql형태이므로 쉽게 이해할수 있을것으로 생각합니다.
- 예제 1. 인라인뷰를 WITH절로 변경처리
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp GROUP BY deptno) dc
WHERE e.deptno = dc.deptno;
--인라인뷰 dc를 with절로 선언하여 처리합니다. 본쿼리에서는 with절서 선언된 dept_count
을 from절에 기술하여 사용하면 됩니다.
WITH dept_count AS
( SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e, dept_count dc
WHERE e.deptno = dc.deptno;
- 예제 2 인라인뷰를 WITH절로 변경하고 본 쿼리에서 반복 재사용
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp GROUP BY deptno) dc1,
emp m,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp GROUP BY deptno) dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
--with절로 변경. 인라인뷰의 동일 sql블럭인 dc1, dc2를 with절로 올려 dept_count
별칭으로 만들어주고 from절에서 참조하여 사용하면 됩니다.
WITH dept_count AS
( SELECT deptno, COUNT(*) AS dept_count
FROM emp GROUP BY deptno)
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
dept_count dc1,
emp m,
dept_count dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
다. 사용예제 실행계획
오라클 DBMS 내부실행 방식 2가지
- Materialize 방식: 내부적으로 오라클 메모리에 임시 테이블을 생성함으로써 반복 재사용
- Inline 방식: 물리적으로 임시 테이블을 생성하지 않으며, 참조된 횟수만큼 런타임 시 반복 수행
오라클 내부동작방식은 2가지가 있는데 INLINE 방식은 WITH절의 문장이 서브쿼리/인라인뷰 형태로 그대로 사용되는 방식입니다. Materialize 방식은 오라클 메모리에 임시테이블을 생성하고 반복하여 접근하는 방식으로 WITH절에서 맨 처음 접근하는 SQL만 DISK IO를 발생하고 그 다음 동일 SQL들은 DISK IO가 아니라 메모리 IO를 발생시키므로 속도가 향상되는 장점이 있습니다. 사용자가 힌트를 지정하지 않으면 옵티마이져가 알아서 동작방식을 선택합니다. Materialize 방식의 With절을 통해 생성된 임시 데이터는 영구적인 오브젝트가 아니어서, With 절을 선언한 SQL문이 실행되는 동안만 유지됩니다. With 절을 2개이상 선언할 수 있으며, With절 내에서 다른 With절을 참조할 수도 있습니다.
현장에서 튜닝지원을 합니다보면 대부분의 SQL을 WITH절로 만들어 사용하는 경우를 종종 봅니다. WITH절은 온라인 트랜잭션이 많은 시스템에서는 가급적 Materialize방식은 피하고 설사 Materialize 방식으로 사용하더라도 처리하는 데이타(몇만건이하 권장)가 많지 않도록 사용하여야 합니다.
다량의 데이타를 Materialize방식으로 임시테이블을 만들게 되면 그 테이블에 접근하는 다른 SQL의 속도가 저하되고 공유메모리에 임시테이블을 만들다보니 다른 SQL들의 진행을 더디게 하는 단점이 있습니다. OLTP(사용자가 많고 소량의 데이타를 많이 처리하는 시스템)시스템에서 사용자가 많이 사용하는 테이블을 몇백만건 Materialize으로 만들게 되면 시스템 전체에 성능문제가 발생할수 있으니 극히 주의해야 합니다.
또한 Materialize으로 처리할때 임시테이블을 만드는 동안 본 테이블의 데이타가 변경이 일어나면 변경된 데이타는 임시테이블에 반영되지 않는다. 그래서 조회시점 문제로 인해 Wrong Results(잘못된값) 이 발생할수 있고 from절에 with절을 사용하게 되면 역시 Wrong Results 의 문제가 발생하는 Bug가 낮은 오라클 버젼(10G)에 예고되어 있으니 조심하여야 합니다.
메타링크를 찾아보게 되면 이런 Wrong Result나 Bug로 인해 무서워서 SQL을 못 사용하겠습니다는 생각이 드는데 이런 일은 흔하지 않으므로 본 칼럼에 나오는대로 조심해서 사용하면 큰 문제는 없을것 같습니다. 즉 많이 사용하는 화면의 SQL은 WITH절 Materialize 방식을 피해야 하며 사용 횟수가 적은 화면의 SQL(배치처리성)만 튜닝용도로 사용하는것을 권고합니다. SQL 형태와 성능에 관련 칼럼은 향후에 포스팅하도록 하겠습니다.
참고로 MSSQL, MYSQL, 마리아DB등 일반적인 RDBMS에서 WITH절은 내부적으로 UNION ALL로 만들어져 사용하므로 Materialize 방식은 지원되지 않는다. 실전 튜닝에 나가면 이 Materialize 방식을 사용하여 튜닝하는 경우가 많다. WITH절의 Materialize 방식은 정말 대단한 기능입니다.
가. /*+INLINE*/ 힌트
WITH dept_count AS
(SELECT /*+INLINE*/ deptno, COUNT(*) AS count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.count AS emp_dept_count
FROM emp e, dept_count dc
WHERE e.deptno = dc.deptno;
--실행계획
Execution Plan
---------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=65 Byte
1 0 HASH JOIN (Cost=8 Card=65 Bytes=3K)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
3 1 VIEW (Cost=4 Card=14 Bytes=364)
4 3 HASH (GROUP BY) (Cost=4 Card=14 Bytes=182)
5 4 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 By
나. /*+ MATERIALIZE */ 힌트
WITH dept_count AS
( SELECT /*+ MATERIALIZE */ deptno, COUNT(*)
AS count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.count AS emp_dept_count
FROM emp e, dept_count dc
WHERE e.deptno = dc.deptno;
--실행계획
TEMP TABLE TRANSFORMATION 으로 발생하며 WITH절의 SQL을 SYS_TEMP_0FD9D6632_FCE77
테이블로 임시로 만들어 반복 사용합니다.
Execution Plan
------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=15 Bytes=690)
1 0 TEMP TABLE TRANSFORMATION
2 1 LOAD AS SELECT OF 'SYS_TEMP_0FD9D6632_FCE77'
3 2 HASH (GROUP BY) (Cost=4 Card=14 Bytes=182)
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=182)
5 1 HASH JOIN (Cost=6 Card=15 Bytes=690)
6 5 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=280)
7 5 VIEW (Cost=2 Card=14 Bytes=364)
8 7 TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6632_FCE77' (TABLE (TEMP)) (Cost=2 Card=14 Bytes=182)
3. WITH절 사용 권고사항
가. 동시성이 높은 시스템에서는 Materialize 동작방식은 사용하지 말자
1) 동시성이 매우 높은 SQL 즉 일과중에 사용하는 빈도가 많은 화면들의 SQL들은
조인형태나 스칼라 서브쿼리형태로 사용하는것이 가장 좋습니다.
2) 글로벌 temp table에 데이터를 적재하고 처리하기때문에 컨트롤파일을 읽어야
하므로 control file sequential read OARCLE WAIT EVENT 발생하여 성능문제 발생 가능합니다.
나. 추출건수가 많은 경우에도 materialize 동작방식은 사용하지 말자
1) 글로벌 temp table영역에 많은 데이타를 적재할 경우 성능이 크게 개선되지 않고
다른 문제를 발생시킬수 있습니다.
다. With절의 선언부분은 sql의 가장 앞에 위치시키자.
With절을 FROM절의 인라인뷰에 위치시키는 경우
1) 10.2.0.4 버전에서 데이터 정합성 훼손문제 발견됨.
2) 10.2.0.3 버전에서는 Wrong Result 발견됨
3) 11G 이상 DBMS는 많이 안정화됨.
라. 반복되는 문장이 많고 처리되는 데이터가 작은 경우 사용하자(튜닝용도)
1) 적당한 량의 데이터를 반복 사용하는 경우 Disk IO를 최소화하여 속도 개선
2) SQL길이를 최소화하여 Parsing 속도 개선, 가독성 향상
3) 배치처리의 경우 Parallel 처리를 하여 속도를 향상할수 있습니다
마. Union all, With, Rollup, Decode & Case의 변환 관계를 생각하며 최상의 SQL형태를 사용하자.
이 내용은 행후 포스팅 예정.
4. 결언
오라클 WITH는 매우 중요한 기능입니다. 다른 RDBMS에서도 WITH절은 있으나 내부 동작방식이 오라클과 틀립니다. Materialize동작방식은 SQL 튜너들이 가장 즐겨사용하는 기술일 정도로 성능개선 또한 탁월합니다. 개발자들도 오라클 WITH절의 특징을 알고 SQL을 만들어 보기 바랍니다.