계층형 쿼리 connect by 튜닝 - 편집 역사

Oracle: /* 계층쿼리(connect by 절) 튜닝 포인트 */

2025-04-07T08:12:04Z

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

← 이전 판		2025년 4월 7일 (월) 17:12 판
13번째 줄:		13번째 줄:
	\|+ 계층쿼리(connect by 절) 튜닝 포인트		\|+ 계층쿼리(connect by 절) 튜닝 포인트
	\|-		\|-
	! 항목 !! 설명		! 튜닝포인트 !! 설명
	\|-		\|-
	\| 인덱스 활용 \|\| CONNECT BY에 사용되는 컬럼 (empno, mgr)에 인덱스 생성		\| 인덱스 활용 \|\| CONNECT BY에 사용되는 컬럼 (empno, mgr)에 인덱스 생성

Oracle: /* 계층쿼리(connect by 절) 튜닝 포인트 */

2025-04-07T08:11:16Z

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

@@ 10번째 줄: / 10번째 줄: @@
 == 계층쿼리(connect by 절) 튜닝 포인트 ==
 항목	설명
 === 인덱스 생성 필수 ===
@@ 42번째 줄: / 54번째 줄: @@
 # 불필요한 조건을 WHERE에 두면 Oracle이 먼저 전체 트리를 생성한 후 필터링함.
 # 가능하면 CONNECT BY 절 내에 조건을 넣는 것이 효율적.
 == 예제 ==

Oracle: 새 문서: == 계층쿼리 예시 == SELECT empno, ename, mgr FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr; == 계층쿼리(connect by 절) 튜닝 포인트 == 항목 설명 1. 인덱스 활용 CONNECT BY에 사용되는 컬럼 (empno, mgr)에 인덱스 생성 2. CONNECT_BY_ISCYCLE 사용 사이클(루프) 방지를 통해 무한루프 방지 3. NOCYCLE 옵션 사용 CONNECT BY NOCYCLE PRIOR ... → 무한 루프 방지 4. WHERE 절 위치 CON...

2025-04-07T08:07:05Z

새 문서: == 계층쿼리 예시 == <source lang=sql> SELECT empno, ename, mgr FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr; </source> == 계층쿼리(connect by 절) 튜닝 포인트 == 항목 설명 1. 인덱스 활용 CONNECT BY에 사용되는 컬럼 (empno, mgr)에 인덱스 생성 2. CONNECT_BY_ISCYCLE 사용 사이클(루프) 방지를 통해 무한루프 방지 3. NOCYCLE 옵션 사용 CONNECT BY NOCYCLE PRIOR ... → 무한 루프 방지 4. WHERE 절 위치 CON...

새 문서

== 계층쿼리 예시 ==
<source lang=sql>
SELECT empno, ename, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
</source>

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

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

=== 인덱스 생성 필수 ===
<source lang=sql>
CREATE INDEX idx_emp_mgr ON emp(mgr);
CREATE INDEX idx_emp_empno ON emp(empno);
</source>

=== NOCYCLE & CONNECT_BY_ISCYCLE ===
<source lang=sql>
SELECT empno, ename, mgr, CONNECT_BY_ISCYCLE
FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE PRIOR empno = mgr;
</source>

=== level 로 깊이 제한 ===
<source lang=sql>
WHERE LEVEL <= 5
</source>

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

== 예제 ==
<source lang=sql>
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;
</source>

<source lang=sql>
SELECT LEVEL, emp_id, emp_name, mgr_id
FROM org_tree
START WITH mgr_id IS NULL
CONNECT BY PRIOR emp_id = mgr_id;
</source>

<source lang=sql>
CREATE INDEX idx_org_tree_mgr_id ON org_tree(mgr_id);
</source>

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

<source lang=sql>
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);
</source>

<source lang=sql>
----------------------------------------------------------------------------------
| 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% |
----------------------------------------------------------------------------------
</source>