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

{section}
{column:width=10}

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

{column}
{column}{column:width=80}{column}
{section}

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

• 조인집합에 대한 계층형 쿼리 실행 시 에러 발생(8i까지)
  {section}
  {column:width=10}

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

{column}
{column}{column:width=80}{column}
{section}

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

{section}
{column:width=10}

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

{column}
{column}{column:width=80}{column}
{section}

About Doc.

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