계층형 쿼리 connect by 튜닝

계층쿼리 예시

SELECT empno, ename, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

계층쿼리(connect by 절) 튜닝 포인트

계층쿼리(connect by 절) 튜닝 포인트

튜닝포인트	설명
인덱스 활용	CONNECT BY에 사용되는 컬럼 (empno, mgr)에 인덱스 생성
CONNECT_BY_ISCYCLE 사용	사이클(루프) 방지를 통해 무한루프 방지
NOCYCLE 옵션 사용	CONNECT BY NOCYCLE PRIOR ... → 무한 루프 방지
WHERE 절 위치	CONNECT BY 안에 넣을 조건 vs WHERE 절에 둘 조건 구분
LEVEL 컬럼 활용	쿼리 깊이를 제한 (WHERE LEVEL <= 5) 하여 성능 확보
병렬처리 제한	계층 쿼리는 병렬 실행이 제한적이므로 주의 필요
MATERIALIZE 힌트	중첩 서브쿼리 사용 시 /*+ MATERIALIZE */ 힌트 고려

항목 설명

인덱스 생성 필수

CREATE INDEX idx_emp_mgr ON emp(mgr);
CREATE INDEX idx_emp_empno ON emp(empno);

NOCYCLE & CONNECT_BY_ISCYCLE

SELECT empno, ename, mgr, CONNECT_BY_ISCYCLE
FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE PRIOR empno = mgr;

level 로 깊이 제한

WHERE LEVEL <= 5

where 절의 조건의 위치

1. 불필요한 조건을 WHERE에 두면 Oracle이 먼저 전체 트리를 생성한 후 필터링함.
2. 가능하면 CONNECT BY 절 내에 조건을 넣는 것이 효율적.

예제

CREATE TABLE org_tree (
    emp_id     NUMBER PRIMARY KEY,
    emp_name   VARCHAR2(50),
    mgr_id     NUMBER
);

INSERT INTO org_tree VALUES (1, 'CEO', NULL);
INSERT INTO org_tree VALUES (2, 'VP1', 1);
INSERT INTO org_tree VALUES (3, 'VP2', 1);
INSERT INTO org_tree VALUES (4, 'Manager1', 2);
INSERT INTO org_tree VALUES (5, 'Manager2', 2);
INSERT INTO org_tree VALUES (6, 'Staff1', 4);
INSERT INTO org_tree VALUES (7, 'Staff2', 4);
COMMIT;

SELECT LEVEL, emp_id, emp_name, mgr_id
FROM org_tree
START WITH mgr_id IS NULL
CONNECT BY PRIOR emp_id = mgr_id;

CREATE INDEX idx_org_tree_mgr_id ON org_tree(mgr_id);

• 이 인덱스는 CONNECT BY PRIOR emp_id = mgr_id 조건에서 자식에서 부모로 탐색할 때 사용됨.

EXPLAIN PLAN FOR
SELECT LEVEL, emp_id, emp_name, mgr_id
FROM org_tree
START WITH mgr_id IS NULL
CONNECT BY PRIOR emp_id = mgr_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |     5 (100)|
|   1 |  CONNECT BY WITH FILTERING   |                  |       |            |
|   2 |   TABLE ACCESS FULL          | ORG_TREE         |     1 |     2   0% |
|   3 |   NESTED LOOPS               |                  |       |            |
|   4 |    CONNECT BY PUMP           |                  |       |            |
|   5 |    TABLE ACCESS BY INDEX ROWID| ORG_TREE        |     1 |     2   0% |
|   6 |     INDEX RANGE SCAN         | IDX_ORG_TREE_MGR |     1 |     1   0% |
----------------------------------------------------------------------------------