본문 바로가기
DBMS/ORACLE

WITH 문 사용(임시테이블, 프로시저, 펑션) 및 동작방식

by 드바 2022. 7. 1.

- WITH 절은 2가지 동작 방식이 있다.
Materialize 방식 : 임시 테이블을 생성 후 WITH 절의 결과를 저장하며, 반복 호출 시 쿼리를 실행하지 않고 임시 테이블에 저장된 결과를 사용한다.
Inline 방식 : 임시 테이블을 생성하지 않고 Inline View 형태로 수행하며, 참조된 횟수만큼 반복적으로 쿼리를 실행한다.
- 동작 방식은 (MATERIALIZE, INLINE) 힌트로 제어가능하다.
- 힌트 없을 시 2번 이상 수행되면 Materialize, 1회만 수행되면 Inline View 로 결정됨

 

WITH 절 임시테이블 선언

WITH EMP_W1 AS
(SELECT DEPARTMENT_ID, SUM(SALARY) AS SAL 
 FROM HR.EMPLOYEES
 GROUP BY DEPARTMENT_ID
)
SELECT A.DEPARTMENT_ID
, B.DEPARTMENT_NAME
, A.SAL
FROM EMP_W1 A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
;

 

WITH 절 여러개 테이블 선언(쉼표로 구분)

WITH EMP_W1 AS
( 
  SELECT DEPARTMENT_ID
       , AVG(SALARY) AS SAL_AVG
    FROM HR.EMPLOYEES
   GROUP BY DEPARTMENT_ID
), 
EMP_W2 AS 
(
  SELECT JOB_ID
       , AVG(SALARY) AS SAL_AVG
    FROM HR.EMPLOYEES
   GROUP BY JOB_ID
)
SELECT A.FIRST_NAME
     , A.JOB_ID
     , A.SALARY
     , B.SAL_AVG AS DEPT_AVG
     , C.SAL_AVG AS JOB_AVG
  FROM HR.EMPLOYEES A
     , EMP_W1 B
     , EMP_W2 C
 WHERE A.EMPLOYEE_ID = 150
   AND A.DEPARTMENT_ID = B.DEPARTMENT_ID
   AND A.JOB_ID = C.JOB_ID
;

 

WITH 절 펑션 선언

WITH
   FUNCTION FN_GET_DEPT_NAME(P_DEPTNO IN NUMBER)
      RETURN VARCHAR2
   IS
      V_DNAME VARCHAR2(14);
   BEGIN
      SELECT DEPARTMENT_NAME
        INTO V_DNAME
        FROM HR.DEPARTMENTS
       WHERE DEPARTMENT_ID = P_DEPTNO;
      RETURN V_DNAME;
   END;
SELECT FIRST_NAME
     , DEPARTMENT_ID
     , FN_GET_DEPT_NAME(DEPARTMENT_ID) AS DNAME
  FROM HR.EMPLOYEES
 WHERE EMPLOYEE_ID = 150
/

FIRST_NAME	     DEPARTMENT_ID DNAME
-------------------- ------------- --------------------
Peter				80 Sales

SQL>

 

WITH 절 프로시저 선언(프로시저는 펑션내에서 호출하는 방식으로 사용 가능)

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 DEPARTMENT_NAME
        INTO V_DNAME
        FROM HR.DEPARTMENTS
       WHERE DEPARTMENT_ID = P_DEPTNO;
-- WITH 절에 선언된 프로시저 호출 가능
      WITH_PROCEDURE(V_DNAME);
      RETURN V_DNAME;
   END;
SELECT FIRST_NAME
     , DEPARTMENT_ID
     , FN_GET_DEPT_NAME(DEPARTMENT_ID) AS DNAME
  FROM HR.EMPLOYEES
 WHERE EMPLOYEE_ID = 150
/


FIRST_NAME	     DEPARTMENT_ID DNAME
-------------------- ------------- --------------------
Peter				80 Sales

P_DNAME=Sales
SQL>

 

 

WITH 절 동작방식 차이

-- Materialize 방식(EMPLOYEES 테이블 처음 한번만 조회 후 만들어진 임시테이블 사용)

WITH EMP_W1 AS
( 
  SELECT /*+ MATERIALIZE */
        DEPARTMENT_ID
       , SUM(SALARY) AS SAL
    FROM HR.EMPLOYEES
   GROUP BY DEPARTMENT_ID
)
SELECT A.DEPARTMENT_ID
     , B.DEPARTMENT_NAME
     , A.SAL
  FROM EMP_W1 A
     , HR.DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
UNION ALL 
SELECT A.DEPARTMENT_ID
     , B.DEPARTMENT_NAME
     , A.SAL
  FROM EMP_W1 A
     , HR.DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
UNION ALL 
SELECT A.DEPARTMENT_ID
     , B.DEPARTMENT_NAME
     , A.SAL
  FROM EMP_W1 A
     , HR.DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
;
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				 | Name 		     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			 |			     |	    1 |        |       |    15 (100)|	       |     33 |00:00:00.01 |	    20 |       |       |	  |
|   1 |  TEMP TABLE TRANSFORMATION		 |			     |	    1 |        |       |	    |	       |     33 |00:00:00.01 |	    20 |       |       |	  |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660E_AEA9C7 |	    1 |        |       |	    |	       |      0 |00:00:00.01 |	     7 |  1024 |  1024 |	  |
|   3 |    HASH GROUP BY			 |			     |	    1 |     11 |    77 |     4	(25)| 00:00:01 |     12 |00:00:00.01 |	     6 |  1186K|  1186K| 2298K (0)|
|   4 |     TABLE ACCESS FULL			 | EMPLOYEES		     |	    1 |    107 |   749 |     3	 (0)| 00:00:01 |    107 |00:00:00.01 |	     6 |       |       |	  |
|   5 |   UNION-ALL				 |			     |	    1 |        |       |	    |	       |     33 |00:00:00.01 |	    12 |       |       |	  |
|   6 |    MERGE JOIN				 |			     |	    1 |     10 |   420 |     5	(20)| 00:00:01 |     11 |00:00:00.01 |	     4 |       |       |	  |
|   7 |     TABLE ACCESS BY INDEX ROWID 	 | DEPARTMENTS		     |	    1 |     27 |   432 |     2	 (0)| 00:00:01 |     27 |00:00:00.01 |	     4 |       |       |	  |
|   8 |      INDEX FULL SCAN			 | DEPT_ID_PK		     |	    1 |     27 |       |     1	 (0)| 00:00:01 |     27 |00:00:00.01 |	     2 |       |       |	  |
|*  9 |     SORT JOIN				 |			     |	   27 |     11 |   286 |     3	(34)| 00:00:01 |     11 |00:00:00.01 |	     0 |  2048 |  2048 | 2048  (0)|
|  10 |      VIEW				 |			     |	    1 |     11 |   286 |     2	 (0)| 00:00:01 |     12 |00:00:00.01 |	     0 |       |       |	  |
|  11 |       TABLE ACCESS FULL 		 | SYS_TEMP_0FD9D660E_AEA9C7 |	    1 |     11 |    77 |     2	 (0)| 00:00:01 |     12 |00:00:00.01 |	     0 |       |       |	  |
|  12 |    MERGE JOIN				 |			     |	    1 |     10 |   420 |     5	(20)| 00:00:01 |     11 |00:00:00.01 |	     4 |       |       |	  |
|  13 |     TABLE ACCESS BY INDEX ROWID 	 | DEPARTMENTS		     |	    1 |     27 |   432 |     2	 (0)| 00:00:01 |     27 |00:00:00.01 |	     4 |       |       |	  |
|  14 |      INDEX FULL SCAN			 | DEPT_ID_PK		     |	    1 |     27 |       |     1	 (0)| 00:00:01 |     27 |00:00:00.01 |	     2 |       |       |	  |
|* 15 |     SORT JOIN				 |			     |	   27 |     11 |   286 |     3	(34)| 00:00:01 |     11 |00:00:00.01 |	     0 |  2048 |  2048 | 2048  (0)|
|  16 |      VIEW				 |			     |	    1 |     11 |   286 |     2	 (0)| 00:00:01 |     12 |00:00:00.01 |	     0 |       |       |	  |
|  17 |       TABLE ACCESS FULL 		 | SYS_TEMP_0FD9D660E_AEA9C7 |	    1 |     11 |    77 |     2	 (0)| 00:00:01 |     12 |00:00:00.01 |	     0 |       |       |	  |
|  18 |    MERGE JOIN				 |			     |	    1 |     10 |   420 |     5	(20)| 00:00:01 |     11 |00:00:00.01 |	     4 |       |       |	  |
|  19 |     TABLE ACCESS BY INDEX ROWID 	 | DEPARTMENTS		     |	    1 |     27 |   432 |     2	 (0)| 00:00:01 |     27 |00:00:00.01 |	     4 |       |       |	  |
|  20 |      INDEX FULL SCAN			 | DEPT_ID_PK		     |	    1 |     27 |       |     1	 (0)| 00:00:01 |     27 |00:00:00.01 |	     2 |       |       |	  |
|* 21 |     SORT JOIN				 |			     |	   27 |     11 |   286 |     3	(34)| 00:00:01 |     11 |00:00:00.01 |	     0 |  2048 |  2048 | 2048  (0)|
|  22 |      VIEW				 |			     |	    1 |     11 |   286 |     2	 (0)| 00:00:01 |     12 |00:00:00.01 |	     0 |       |       |	  |
|  23 |       TABLE ACCESS FULL 		 | SYS_TEMP_0FD9D660E_AEA9C7 |	    1 |     11 |    77 |     2	 (0)| 00:00:01 |     12 |00:00:00.01 |	     0 |       |       |	  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

-- Inline 방식(메인 쿼리에서 EMPLOYEES 테이블 계속 조회 함)

WITH EMP_W1 AS
( 
  SELECT /*+ INLINE */
        DEPARTMENT_ID
       , SUM(SALARY) AS SAL
    FROM HR.EMPLOYEES
   GROUP BY DEPARTMENT_ID
)
SELECT A.DEPARTMENT_ID
     , B.DEPARTMENT_NAME
     , A.SAL
  FROM EMP_W1 A
     , HR.DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
UNION ALL
SELECT A.DEPARTMENT_ID
     , B.DEPARTMENT_NAME
     , A.SAL
  FROM EMP_W1 A
     , HR.DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
UNION ALL
SELECT A.DEPARTMENT_ID
     , B.DEPARTMENT_NAME
     , A.SAL
  FROM EMP_W1 A
     , HR.DEPARTMENTS B
 WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
 ;

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	   1 |	      |       |    21 (100)|	      |     33 |00:00:00.01 |	   30 |       |       | 	 |
|   1 |  UNION-ALL		      | 	    |	   1 |	      |       | 	   |	      |     33 |00:00:00.01 |	   30 |       |       | 	 |
|   2 |   MERGE JOIN		      | 	    |	   1 |	   11 |   462 |     7  (29)| 00:00:01 |     11 |00:00:00.01 |	   10 |       |       | 	 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	   1 |	   27 |   432 |     2	(0)| 00:00:01 |     27 |00:00:00.01 |	    4 |       |       | 	 |
|   4 |     INDEX FULL SCAN	      | DEPT_ID_PK  |	   1 |	   27 |       |     1	(0)| 00:00:01 |     27 |00:00:00.01 |	    2 |       |       | 	 |
|*  5 |    SORT JOIN		      | 	    |	  27 |	   11 |   286 |     5  (40)| 00:00:01 |     11 |00:00:00.01 |	    6 |  2048 |  2048 | 2048  (0)|
|   6 |     VIEW		      | 	    |	   1 |	   11 |   286 |     4  (25)| 00:00:01 |     12 |00:00:00.01 |	    6 |       |       | 	 |
|   7 |      HASH GROUP BY	      | 	    |	   1 |	   11 |    77 |     4  (25)| 00:00:01 |     12 |00:00:00.01 |	    6 |  1186K|  1186K| 2355K (0)|
|   8 |       TABLE ACCESS FULL       | EMPLOYEES   |	   1 |	  107 |   749 |     3	(0)| 00:00:01 |    107 |00:00:00.01 |	    6 |       |       | 	 |
|   9 |   MERGE JOIN		      | 	    |	   1 |	   11 |   462 |     7  (29)| 00:00:01 |     11 |00:00:00.01 |	   10 |       |       | 	 |
|  10 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	   1 |	   27 |   432 |     2	(0)| 00:00:01 |     27 |00:00:00.01 |	    4 |       |       | 	 |
|  11 |     INDEX FULL SCAN	      | DEPT_ID_PK  |	   1 |	   27 |       |     1	(0)| 00:00:01 |     27 |00:00:00.01 |	    2 |       |       | 	 |
|* 12 |    SORT JOIN		      | 	    |	  27 |	   11 |   286 |     5  (40)| 00:00:01 |     11 |00:00:00.01 |	    6 |  2048 |  2048 | 2048  (0)|
|  13 |     VIEW		      | 	    |	   1 |	   11 |   286 |     4  (25)| 00:00:01 |     12 |00:00:00.01 |	    6 |       |       | 	 |
|  14 |      HASH GROUP BY	      | 	    |	   1 |	   11 |    77 |     4  (25)| 00:00:01 |     12 |00:00:00.01 |	    6 |  1186K|  1186K| 2355K (0)|
|  15 |       TABLE ACCESS FULL       | EMPLOYEES   |	   1 |	  107 |   749 |     3	(0)| 00:00:01 |    107 |00:00:00.01 |	    6 |       |       | 	 |
|  16 |   MERGE JOIN		      | 	    |	   1 |	   11 |   462 |     7  (29)| 00:00:01 |     11 |00:00:00.01 |	   10 |       |       | 	 |
|  17 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	   1 |	   27 |   432 |     2	(0)| 00:00:01 |     27 |00:00:00.01 |	    4 |       |       | 	 |
|  18 |     INDEX FULL SCAN	      | DEPT_ID_PK  |	   1 |	   27 |       |     1	(0)| 00:00:01 |     27 |00:00:00.01 |	    2 |       |       | 	 |
|* 19 |    SORT JOIN		      | 	    |	  27 |	   11 |   286 |     5  (40)| 00:00:01 |     11 |00:00:00.01 |	    6 |  2048 |  2048 | 2048  (0)|
|  20 |     VIEW		      | 	    |	   1 |	   11 |   286 |     4  (25)| 00:00:01 |     12 |00:00:00.01 |	    6 |       |       | 	 |
|  21 |      HASH GROUP BY	      | 	    |	   1 |	   11 |    77 |     4  (25)| 00:00:01 |     12 |00:00:00.01 |	    6 |  1186K|  1186K| 2355K (0)|
|  22 |       TABLE ACCESS FULL       | EMPLOYEES   |	   1 |	  107 |   749 |     3	(0)| 00:00:01 |    107 |00:00:00.01 |	    6 |       |       | 	 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

'DBMS > ORACLE' 카테고리의 다른 글

특정 SQL_ID shared pool 에서 flush 하기  (0) 2022.09.05
OPTIMIZER_DYNAMIC_SAMPLING  (0) 2022.07.06
TTS (Transportable Tablespaces)  (0) 2022.06.24
listener.ora / sqlnet.ora / tnsnames.ora 차이점  (0) 2022.06.20
패스워드 특수문자 포함 sqlplus 접속 방법  (0) 2022.06.17

관련글

댓글

티스토리툴바