BOM, 조직도 등을 개발할때 Hierarchical Queries 는 필수이다.
하지만 이상하게도 Hierarchical Queries는 개발자들의 기피대상 1위이다.
Self 조인을 사용한다든지 아니면 Recursive Function 등을 사용하는 등등 어떻게든 Hierarchical Query 를
피해갈려고 애쓴다.
9I 및 10g 에서 Connect By Query 는 이미 피해갈수 없을 정도로 막강해졌다.
즉 예전과는 달리 Connect By 가 워낙 막강해져서 이젠 이기능을 모방하려면 엄청난 노가다(?) 를 해야만 할것이다.
여기서는 기본적인 Connect By 문법은 이야기 하지않고 9i , 10g 에서 추가된 문법과 수정된 Connect By 관련
Plan 에 대해서만 이야기 할 것이다.
9i 신기능
Hierarchical Queries 관련하여 대표적인 미신이 2가지 있다.
1) 조인관련 신기능
첫번째는 조인을 할수가 없다는것이고 두번째는 조인이 포함된 뷰를 사용할수없다는 것이다.
이것은 8i 까지만 사실이고 9i 부터는 자유롭게 from 절을 이용하여 조인할수 있다.
SELECT LEVEL,
LPAD(' ',2*(LEVEL - 1)) || lname as ename,
A.emp_id,
A.manager_emp_id,
B.name as deptname
FROM employee A, department B
WHERE A.dept_id = b.dept_id
START WITH A.emp_id = 7839
CONNECT BY PRIOR A.emp_id = A.manager_emp_id;
--결과
2) 향상된 order by
Hierarchical Queries 의 문제는 order by 를 하면 계층구조가 깨진다는 것이다.
따라서 ORDER BY 를 하려면 인덱스 설계를 잘하는수 밖에 없었다.
하지만 9i 부터 ORDER SIBLINGS BY 절이 나오면서 그런 문제는 사라졌다.
다시말하면 계층구조가 다치지 않는 범위내에서 SORT 하는 기능이 추가 되었다.
SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || lname ename,
emp_id, manager_emp_id
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id
ORDER SIBLINGS BY lname;
--결과
3) Path 나타내기
계층구조를 한줄로 나타내는 기능이 9i 에서 추가 되었다.
SELECT SYS_CONNECT_BY_PATH(lname, '#')
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id;
--결과
10g 신기능
1) Root 노드와 Leaf 노드를 구분하는 기능이 추가되었다.
CONNECT_BY_ROOT 는 반드시 컬럼을 지정하여야 하고
CONNECT_BY_ISLEAF 는 여부(1 이면 Y 라는 뜻이다)이다.
SELECT lname "Employee",
CONNECT_BY_ROOT lname "Top Manager",
CONNECT_BY_ISLEAF
FROM employee
START WITH manager_emp_id = 7839
CONNECT BY PRIOR emp_id = manager_emp_id;
--결과
2) CYCLE(무한루프) 방지 및 CYCLE 확인기능
먼저 무한루프를 만들기 위하여 데이터를 수정한다.
UPDATE employee
SET manager_emp_id = 7654 --> ROOT 노드(KING)의 매니져를 둠으로써 무한루프 발생
WHERE manager_emp_id IS NULL;
COMMIT;
SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || lname "EMPLOYEE",
emp_id, manager_emp_id
FROM employee
START WITH emp_id = 7839
CONNECT BY PRIOR emp_id = manager_emp_id;
ERROR:
ORA-01436: CONNECT BY loop in user data --> 무한루프가 발생한다.
그럼 해결책은?
아래처럼 하면된다.
아래는 반복을 제거하고 결과를 리턴한다.
SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || lname "EMPLOYEE",
CONNECT_BY_ISCYCLE, --> CYCLE 에러의 발생지점을 알려준다.
emp_id, manager_emp_id
FROM employee
START WITH emp_id = 7839
CONNECT BY NOCYCLE PRIOR emp_id = manager_emp_id; --> 무한루프를 방지한다.
--결과
3) PLAN 상의 변경
추가적으로 PLAN 에서의 변경사항을 살펴보면
10.2.0.3 버젼까지 튜너들을 괴롭히던 원인을 알수없는 FULL SCAN 이 사라졌다.
--10.2.0.3 PLAN
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=2 Bytes=22)
1 0 CONNECT BY (WITH FILTERING)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE)
3 2 INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=3)
4 1 NESTED LOOPS
5 4 BUFFER (SORT)
6 5 CONNECT BY PUMP
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)
8 7 INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)
9 1 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)
위의 FULL SCAN(굵은글씨) 은 있을수 없는 정보(BUG)였는데 이는 10.2.0.4 에서 PLAN 상에서 사라졌다.
--10.2.0.4 PLAN
Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=2 Bytes=22)
1 0 CONNECT BY (WITH FILTERING)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE)
3 2 INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=3)
4 1 NESTED LOOPS
5 4 BUFFER (SORT)
6 5 CONNECT BY PUMP
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)
8 7 INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)
4) 파라미터의 변화
_optimizer_connect_by_cost_based 파라미터가 추가 되었다.
하지만 이파라미터는 버그가 보고되고 있어 사이트에서 수동으로 false 로 바꾸어 놓는 경우가 많다.
false 로 바꾸면 connect by 쿼리에서 조인하는 경우( 9i 의1) 조인관련 신기능 참조 ) FTS 로 PLAN 이 바뀌는 경향이 있으므로 주의 해야한다.
아례 예제를 보자.
SELECT LEVEL,
LPAD(' ',2*(LEVEL - 1)) || lname as ename,
A.emp_id,
A.manager_emp_id,
B.name as deptname
FROM employee A, department B
WHERE A.dept_id = b.dept_id
START WITH A.emp_id = 7839
CONNECT BY PRIOR A.emp_id = A.manager_emp_id;
Execution Plan : --> alter session set "_optimizer_connect_by_cost_based" = true; 적용한 plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=2 Bytes=50)
1 0 CONNECT BY (WITH FILTERING)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=34)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=1 Bytes=22)
4 3 INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=4 Bytes=48)
6 5 INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
7 1 NESTED LOOPS (Cost=2 Card=2 Bytes=50)
8 7 NESTED LOOPS
9 8 BUFFER (SORT)
10 9 CONNECT BY PUMP
11 8 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=26)
12 11 INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)
13 7 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
14 13 INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
15 1 MERGE JOIN (Cost=5 Card=14 Bytes=434)
16 15 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=4 Bytes=48)
17 16 INDEX (FULL SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=4)
18 15 SORT (JOIN) (Cost=4 Card=14 Bytes=266)
19 18 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card=14 Bytes=266)
Execution Plan --> alter session set "_optimizer_connect_by_cost_based" = false; 적용한 plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1 Bytes=25)
1 0 CONNECT BY (WITH FILTERING)
2 1 FILTER
3 2 COUNT
4 3 NESTED LOOPS (Cost=3 Card=1 Bytes=25)
5 4 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
7 6 INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
8 1 HASH JOIN
9 8 CONNECT BY PUMP
10 8 COUNT
11 10 NESTED LOOPS (Cost=3 Card=1 Bytes=25)
12 11 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
13 11 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
14 13 INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
15 1 COUNT
16 15 NESTED LOOPS (Cost=3 Card=1 Bytes=25)
17 16 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
18 16 TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
19 18 INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
결론:
Hierarchical Queries는 조직도나 tree 구조의 BOM 업무의 거의 모든 기능을 커버한다.
위의 문법은 아주 기초적인 것들이니 반드시 이해하고 업무에 활용하기 바란다.
