특정 행의 계층 경로 조회

특정 행의 계층 경로를 조회해보자.

아래 쿼리는 전체 계층을 순전개하여 필요한 행을 필터링했다. 테이블 사이즈가 큰 경우 쿼리의 성능이 저하될 수 있다.

-- 1
SELECT     SUBSTR (SYS_CONNECT_BY_PATH (ename, '>'), 2) AS path
      FROM emp
     WHERE empno = 7788
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR empno;

PATH
----------------
KING>JONES>SCOTT

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

---------------------------------------------------------
| Id  | Operation                                | Name |
---------------------------------------------------------
|   0 | SELECT STATEMENT                         |      |
|*  1 |  FILTER                                  |      |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|      |
|   3 |    TABLE ACCESS FULL                     | EMP  |
---------------------------------------------------------

아래 쿼리는 특정 행의 계층을 역전개하고, LISTAGG 함수로 계층 경로를 생성했다. 최소한의 계층 전개로 계층 경로를 조회할 수 있다.

-- 2
SELECT     LISTAGG (ename, '>') WITHIN GROUP (ORDER BY LEVEL DESC) AS path
      FROM emp
START WITH empno = 7788
CONNECT BY empno = PRIOR mgr;

PATH
----------------
KING>JONES>SCOTT

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

-------------------------------------------------
| Id  | Operation                      | Name   |
-------------------------------------------------
|   0 | SELECT STATEMENT               |        |
|   1 |  SORT GROUP BY                 |        |
|*  2 |   CONNECT BY WITH FILTERING    |        |
|   3 |    TABLE ACCESS BY INDEX ROWID | EMP    |
|*  4 |     INDEX UNIQUE SCAN          | PK_EMP |
|   5 |    NESTED LOOPS                |        |
|   6 |     CONNECT BY PUMP            |        |
|   7 |     TABLE ACCESS BY INDEX ROWID| EMP    |
|*  8 |      INDEX UNIQUE SCAN         | PK_EMP |
-------------------------------------------------

[2020-03-04]

아래는 마농님이 작성하신 쿼리다. 집계를 수행하지 않으므로 2번 쿼리에 비해 효율적이다.

-- 3
SELECT     REVERSE (SUBSTR (SYS_CONNECT_BY_PATH (REVERSE (ename), '>'), 2)) AS path
      FROM emp
     WHERE CONNECT_BY_ISLEAF = 1
START WITH empno = 7788
CONNECT BY empno = PRIOR mgr;

PATH
----------------
KING>JONES>SCOTT

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

-------------------------------------------------
| Id  | Operation                      | Name   |
-------------------------------------------------
|   0 | SELECT STATEMENT               |        |
|*  1 |  FILTER                        |        |
|*  2 |   CONNECT BY WITH FILTERING    |        |
|   3 |    TABLE ACCESS BY INDEX ROWID | EMP    |
|*  4 |     INDEX UNIQUE SCAN          | PK_EMP |
|   5 |    NESTED LOOPS                |        |
|   6 |     CONNECT BY PUMP            |        |
|   7 |     TABLE ACCESS BY INDEX ROWID| EMP    |
|*  8 |      INDEX UNIQUE SCAN         | PK_EMP |
-------------------------------------------------