오라클 WITH구문

오라클 WITH구문

​

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=618

 

오라클 WITH구문

오라클 WITH구문5.6 WITH 구문ORACLE9i R2이후 사용 가능한 WITH구문은 이름이 부여된 서브쿼리 블록으로 global temporary tables, virtual table or an inline view 처럼 작동된다. 복잡한 SQL에서 동일 쿼리블록이 반복적으로 사용되는 경우 그 블록에 이름을 부여하여 한곳에서 정의하고 이를 재사용 할…

ojc.asia

​

​

5.6 WITH 구문

ORACLE9i R2이후 사용 가능한 WITH구문은 이름이 부여된 서브쿼리 블록으로 global temporary tables, virtual table or an inline view 처럼 작동된다. 복잡한 SQL에서 동일 쿼리블록이 반복적으로 사용되는 경우 그 블록에 이름을 부여하여 한곳에서 정의하고 이를 재사용 할 수 있게 함으로서 쿼리문 코딩량도 줄이고 성능도 향상 시킬 수 있는데, WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있다. 자주 실행되는 경우 한번만 Parsing되고 실행 계획이 수립되므로 성능 향상에 도움이 된다.

WITH문은 주로 SELECT 문과 함께 사용 되며 Query Block을 미리 정할 수 있는데 자주 사용되는 QUERY의 블록이 고 비용인 경우 이용하면 성능상 장점이 있다.

-- 인라인 뷰를 이용한 경우 SELECT e.ename, emps.emp_count FROM emp e, (SELECT deptno, COUNT(*) AS emp_count FROM emp GROUP BY deptno) emps WHERE e.deptno = emps.deptno; -- with문 이용 WITH emps AS ( SELECT deptno, COUNT(*) AS emp_count FROM emp GROUP BY deptno ) SELECT e.ename, emps.emp_count FROM emp e, emps WHERE e.deptno = emps.deptno; -- 인라인뷰를 이용한 경우 SELECT e.ename AS employee_name, emps1.emp_count , m.ename AS manager_name, emps2.emp_count FROM emp e, (SELECT deptno, COUNT(*) AS emp_count FROM emp GROUP BY deptno) emps1, emp m, (SELECT deptno, COUNT(*) AS emp_count FROM emp GROUP BY deptno) emps2 WHERE e.deptno = emps1.deptno AND e.mgr = m.empno AND m.deptno = emps2.deptno; -- WITH문을 이용한 경우 WITH emp_count AS ( SELECT deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno ) SELECT e.ename, emps1.dept_count, m.ename, emps2.dept_count FROM emp e, emp_count emps1, emp m, emp_count emps2 WHERE e.deptno = emps1.deptno AND e.mgr = m.empno AND m.deptno = emps2.deptno; --아래의 예문을 보면 WITH에 대해 이해가 될 것이다. 먼저 WITH를 사용하지 않은 예문이다. -- 64초 소요 select e.empno, e.deptno, e.sal, d.dname from ( select empno, sal, deptno from myemp1 where sal > 5000000 ) e, mydept1 d where e.deptno = d.deptno and e.deptno = ‘1’ union select e.empno, e.deptno, e.sal, d.dname from ( select empno, sal, deptno from myemp1 where sal > 5000000 ) e, mydept1 d where e.deptno = d.deptno and e.deptno = ‘2’ union select e.empno, e.deptno, e.sal, d.dname from ( select empno, sal, deptno from myemp1 where sal > 5000000 ) e, mydept1 d where e.deptno = d.deptno and e.deptno = ‘3’ -- WITH구를 사용한 예문 -- 33초 소요 with e as ( select empno, sal, deptno from myemp1 where sal > 5000000 ) select e.empno, e.deptno, e.sal, d.dname from e, mydept1 d where e.deptno = d.deptno and e.deptno = ‘1’ union select e.empno, e.deptno, e.sal, d.dname from e, mydept1 d where e.deptno = d.deptno and e.deptno = ‘2’ union select e.empno, e.deptno, e.sal, d.dname from e, mydept1 d where e.deptno = d.deptno and e.deptno = ‘3’ -- INSERT문에서 WITH구가 사용되는 예제 SQL> create table emp20 as select empno, ename from emp where 1 > 100; 테이블이 생성되었습니다. SQL> INSERT INTO emp20 2 SELECT * FROM ( 3 WITH dept20 AS ( 4 SELECT empno, ename FROM emp WHERE deptno = 20) 5 SELECT * FROM dept20); 5 개의 행이 만들어졌습니다. SQL> commit; 커밋이 완료되었습니다.