by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

2. 순환(RECURSIVE)관계 전개 시의 조인




1. 순환(Recursive)관계 SQL : 계층형 쿼리

SQL> SELECT LEVEL lv
  2       , LPAD(' ', (LEVEL-1) * 2) || ename ename
  3       , deptno
  4    FROM emp
  5   START WITH mgr IS NULL       -- 계층쿼리의 루트 지정
  6  CONNECT BY PRIOR empno = mgr  -- 상위(PRIOR) 와 하위간의 관계 지정
  7  ;

        LV ENAME                    DEPTNO
---------- -------------------- ----------
         1 KING                         10
         2   JONES                      20
         3     FORD                     20
         4       SMITH                  20
         2   BLAKE                      30
         3     ALLEN                    30
         3     WARD                     30
         3     MARTIN                   30
         3     TURNER                   30
         3     JAMES                    30
         2   CLARK                      10
         3     MILLER                   10

12 개의 행이 선택되었습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 3364448299

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |    12 |   204 |     3   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
|*  2 |   FILTER                  |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL      | EMP  |    12 |   204 |     3   (0)| 00:00:01 |
|*  4 |   HASH JOIN               |      |       |       |            |          |
|   5 |    CONNECT BY PUMP        |      |       |       |            |          |
|   6 |    TABLE ACCESS FULL      | EMP  |    12 |   204 |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL       | EMP  |    12 |   204 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MGR" IS NULL)
   2 - filter("MGR" IS NULL)
   4 - access("MGR"=NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
        795  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         12  rows processed

2. 조인집합에 대한 순환(Recursive) 전개

  • 조인집합에 대한 계층형 쿼리 실행 시 에러 발생(8i까지)
    SQL> SELECT LEVEL lv
      2       , LPAD(' ', (LEVEL-1) * 2) || e.ename ename
      3       , d.dname
      4    FROM emp e, dept d
      5   WHERE e.deptno = d.deptno
      6   START WITH e.mgr IS NULL
      7  CONNECT BY PRIOR e.empno = e.mgr
      8  ;
    
            LV ENAME                DNAME
    ---------- -------------------- ----------------------------
             1 KING                 ACCOUNTING
             2   JONES              RESEARCH
             3     FORD             RESEARCH
             4       SMITH          RESEARCH
             2   BLAKE              SALES
             3     ALLEN            SALES
             3     WARD             SALES
             3     MARTIN           SALES
             3     TURNER           SALES
             3     JAMES            SALES
             2   CLARK              ACCOUNTING
             3     MILLER           ACCOUNTING
    
    12 개의 행이 선택되었습니다.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3513732447
    
    -------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |         |    12 |   360 |     4   (0)| 00:00:01 |
    |*  1 |  CONNECT BY WITH FILTERING      |         |       |       |            |          |
    |*  2 |   FILTER                        |         |       |       |            |          |
    |   3 |    COUNT                        |         |       |       |            |          |
    |   4 |     NESTED LOOPS                |         |    12 |   360 |     4   (0)| 00:00:01 |
    |   5 |      TABLE ACCESS FULL          | EMP     |    12 |   204 |     3   (0)| 00:00:01 |
    |   6 |      TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
    |*  7 |       INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
    |*  8 |   HASH JOIN                     |         |       |       |            |          |
    |   9 |    CONNECT BY PUMP              |         |       |       |            |          |
    |  10 |    COUNT                        |         |       |       |            |          |
    |  11 |     NESTED LOOPS                |         |    12 |   360 |     4   (0)| 00:00:01 |
    |  12 |      TABLE ACCESS FULL          | EMP     |    12 |   204 |     3   (0)| 00:00:01 |
    |  13 |      TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
    |* 14 |       INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
    |  15 |   COUNT                         |         |       |       |            |          |
    |  16 |    NESTED LOOPS                 |         |    12 |   360 |     4   (0)| 00:00:01 |
    |  17 |     TABLE ACCESS FULL           | EMP     |    12 |   204 |     3   (0)| 00:00:01 |
    |  18 |     TABLE ACCESS BY INDEX ROWID | DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
    |* 19 |      INDEX UNIQUE SCAN          | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("E"."MGR" IS NULL)
       2 - filter("E"."MGR" IS NULL)
       7 - access("E"."DEPTNO"="D"."DEPTNO")
       8 - access("E"."MGR"=NULL)
      14 - access("E"."DEPTNO"="D"."DEPTNO")
      19 - access("E"."DEPTNO"="D"."DEPTNO")
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            102  consistent gets
              0  physical reads
              0  redo size
            795  bytes sent via SQL*Net to client
            384  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              5  sorts (memory)
              0  sorts (disk)
             12  rows processed
    

2. 조인집합에 대한 순환(Recursive) 전개 오류 해결

SQL> SELECT e.lv
  2       , LPAD(' ', (e.lv - 1) * 2) || e.ename ename
  3       , d.dname
  4    FROM (SELECT LEVEL lv
  5               , ename
  6               , deptno
  7            FROM emp
  8           START WITH mgr IS NULL
  9          CONNECT BY PRIOR empno = mgr
 10          ) e, dept d
 11   WHERE e.deptno = d.deptno
 12  ;

        LV ENAME                DNAME
---------- -------------------- ----------------------------
         1 KING                 ACCOUNTING
         2   JONES              RESEARCH
         3     FORD             RESEARCH
         4       SMITH          RESEARCH
         2   BLAKE              SALES
         3     ALLEN            SALES
         3     WARD             SALES
         3     MARTIN           SALES
         3     TURNER           SALES
         3     JAMES            SALES
         2   CLARK              ACCOUNTING
         3     MILLER           ACCOUNTING

12 개의 행이 선택되었습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 1913249162

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    12 |   552 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    12 |   552 |     4   (0)| 00:00:01 |
|   2 |   VIEW                       |         |    12 |   396 |     3   (0)| 00:00:01 |
|*  3 |    CONNECT BY WITH FILTERING |         |       |       |            |          |
|*  4 |     FILTER                   |         |       |       |            |          |
|   5 |      TABLE ACCESS FULL       | EMP     |    12 |   204 |     3   (0)| 00:00:01 |
|*  6 |     HASH JOIN                |         |       |       |            |          |
|   7 |      CONNECT BY PUMP         |         |       |       |            |          |
|   8 |      TABLE ACCESS FULL       | EMP     |    12 |   204 |     3   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL        | EMP     |    12 |   204 |     3   (0)| 00:00:01 |
|  10 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|* 11 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("MGR" IS NULL)
   4 - filter("MGR" IS NULL)
   6 - access("MGR"=NULL)
  11 - access("E"."DEPTNO"="D"."DEPTNO")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         49  consistent gets
          0  physical reads
          0  redo size
        795  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         12  rows processed

About Doc.

  • 최초작성자 : 기민용
  • 최초작성일 : 2009년 5월 15일
  • 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션2를 참고했습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.