데이터베이스 질문과 답변
by 신이만든짝퉁 [Oracle 기초] 계층적 구조 hierarchical query [2013.07.29 13:05:52]
안녕하세요~
요즘 계층구조 쿼리를 보고 있는데, 이해가 잘 안가는 부분이 있어서 머리가 아픕니다.
많은 것이 궁금합니다만 제일 궁금한 부분은 내부 동작원리입니다.
과연 계층구조 쿼리는 어떤순서로 실행이 되는지 아시는 분들 계시면, 제발 속 시원한 설명 부탁드립니다.
(내용이 많이 깁니다. 읽어주시는 분들께 미리 감사 말씀드립니다.)
저는 아래와 같은 방법으로 이해를 했습니다
혹시 잘못된 곳이 있다면 지적 부탁드리겠습니다.
SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ename ename,
PRIOR ename mgrname,
empno, mgr, job
FROM emp
START WITH job='PRESIDENT'
CONNECT BY PRIOR empno=mgr;
LEVEL ENAME MGRNAME EMPNO MGR JOB
------- -------------------- ---------- ---------- ---------- ---------
1 KING 7839 PRESIDENT
2 JONES KING 7566 7839 MANAGER
3 SCOTT JONES 7788 7566 ANALYST
4 ADAMS SCOTT 7876 7788 CLERK
3 FORD JONES 7902 7566 ANALYST
2 BLAKE KING 7698 7839 MANAGER
3 MARTIN BLAKE 7654 7698 SALESMAN
3 TURNER BLAKE 7844 7698 SALESMAN
3 JAMES BLAKE 7900 7698 CLERK
2 CLARK KING 7782 7839 MANAGER
3 MILLER CLARK 7934 7782 CLERK
1. 시작지점은 job='PRESIDENT'
-> 여기서는 KING 밖에 없으므로 시작지점은 KING으로만 시작
KING
2. 상위컬럼에 해당하는 KING을 제외한 나머지 행(ROW)중 mgr이 KING의 empno와 동일한 행을 찾음
-> 여기서는 JONES, BLAKE, CLARK 가 있음
KING
-JONES
-BLAKE
-CLARK
3. 다시 JONES을 시작점으로 나머지 행 중 mgr이 JONES의 empno와 동일한 행을 찾음
-> 여기서는 SCOTT, FORD가 있음
--> 여기서 궁금한 점 :
ㄱ. 상위행에서 하위행을 찾을 때마다 테이블의 풀스캔을 실행하는 건지?
ㄴ. 아니면 최초 풀 스캔(1회) 이후 행의 재배치(ORDER BY)만 이뤄지는 건지?
ㄷ. 계층구조 쿼리절은 반드시 풀스캔이 일어날 수 밖에 없는 구조인 것인지?
KING
-JONES
-SCOTT
-FORD
-BLAKE
-CLARK
4. 다시 SCOTT를 시작점으로 나머지 행 중 mgr이 SCOTT의 empno와 동일한 행을 찾음
-> 여기서는 ADAMS가 있음
KING
-JONES
-SCOTT
-ADAMS
-FORD
-BLAKE
-CLARK
5. 다시 ADAMS를 시작점으로 나머지 행 중 mgr이 ADAMS의 empno와 동일한 행을 찾음
-> 찾을 수 없음, 그렇다면 상위 행으로 돌아감
KING
-JONES
-SCOTT
-ADAMS
-FORD
-BLAKE
-CLARK
6. 이번엔 FORD 를 시작점으로 지금까지의 과정을 반복함
여기서 궁금한 점:
위 순서는 제가 생각하는 계층구조의 쿼리 실행순서 및 방법입니다.
혹시 이 방법말고 다른 방법으로 동작하는 건지요?
제가 잘못 이해하고 있다면 어느부분인지요?
여기저기 인터넷 검색을 통해서 공부를 하고 있습니다만,
오히려 혼란만 가중되어 여기에 이렇게 문의 드려봅니다.
그룹을 만들어가면서 조인할수도 있지않을까요?
첫번째 PRESIDENT와 조인 그룹(MANAGER 등등)
두번째 MANAGER 조인 그룹(ANALYST 등등)
.
.
.
그룹핑하면서 내부적으로 하는 방법도.. 상상의 나래입니다. ㅎㅎ
3. 다시 JONES을 시작점으로 나머지 행 중 mgr이 JONES의 empno와 동일한 행을 찾음
-> 여기서는 SCOTT, FORD가 있음
--> 여기서 궁금한 점 :
ㄱ. 상위행에서 하위행을 찾을 때마다 테이블의 풀스캔을 실행하는 건지?
----> 인덱스가 있다면 인덱스를 타겠죠.
----> empno 인덱스, mgr 인덱스 각각 있는것이 좋습니다.
ㄴ. 아니면 최초 풀 스캔(1회) 이후 행의 재배치(ORDER BY)만 이뤄지는 건지?
----> 이건 아닌 듯
ㄷ. 계층구조 쿼리절은 반드시 풀스캔이 일어날 수 밖에 없는 구조인 것인지?
----> 인덱스가 있다면 인덱스를 타겠죠.
10g 까지는 테이블을 반복적으로 읽는 실행계획이 나오는데요.
11g 부터는 테이블을 한번만 읽는 실행계획이 수립됩니다.
11g 는 진화했네요.
여기까진 질문하신 실행계획이 풀리는거에 대한거구요.
SQL 자체의 실행 순서도 중요합니다.
단독 테이블인 경우는 순서가 다음과 같습니다.
1. from
2. start with
3. connect by
4. where -- (중요포인트)계층구조 전개 후 필터링 합니다.
5. select
그러나 두개 테이블 조인의 경우는 꽤 까다롭게 동작합니다.
1. from
2. Join -- (중요포인트)조인 조건이 먼저 수행되고
3. start with
4. connect by
5. where -- (중요포인트)필터 조건은 나중에 수행 합니다.
6. select
그런데 마농님 말씀듣고나니 또다른 궁금증이 생겼습니다.
먼저 저도 오라클 클럽에 있는 강좌에 있는 계층구조 쿼리의 실행순서를 보고
where 조건이 가장 마지막에 수행된다는 것을 알게됐습니다.
제가 테스트한 디비가 11g여서 그런지, 아니면 데이터의 건수가 적어서 그런지
실행계획상 풀스캔이 이뤄졌습니다.
(말씀듣고 기존 empno 컬럼 이외에 mgr에도 인덱스를 만들어 봤지만 동일했습니다)
저는 단독테이블과 테이블 조인의 경우의 수는 고려하지 못하고
where 조건을 가장 마지막에 사용하기 때문에 저는 풀스캔이 이뤄진다고 생각했습니다.
가. 말씀대로라면 단독테이블의 경우에는 항상 풀테이블 스캔이 일어나야하지 않을까요?
이는 틀린 생각인가요?
데이터를 필터링하지 않고 계층구조를 만들기에 일량이 더욱 늘어날 것 같습니다만,
인라인 뷰 등의 제어를 통해 사용자가 조절할 수 있으니 넘어가도 될 듯합니다.
테이블 조인의 경우 마농님의 말씀대로 인덱스를 사용하는게 합리적이다는 것을 깨닫게 됐습니다.
그런데 마농님 말씀을 듣고 나니 이런생각이 드네요.
단독 테이블이고 START WITH 절이 있고, 이 컬럼에 인덱스가 있으면 인덱스를 사용하고,
없다면 풀스캔 하지 않을까 하고요.
그리고 CONNECT BY에 기술한 조건절에 인덱스가 있다면 이때에도 인덱스를 사용할 수 있겠다고요.
나. START WITH절 컬럼의 인덱스 여부에 따른 실행방식에 차이가 있을까요?
(11g 에서 scott.emp 테이블에서는 동일하게 풀스캔으로 나옵니다.
아래 '라'의 가정이 설득력이 있게 생각됨)
다. 단독테이블에서 계층구조를 만들 때 CONNECT BY에 기술한 각각의 컬럼에 인덱스가 존재한다면
1개 테이블의 2개 인덱스를 모두 쓴다는 말인가요?
라. 만약 어떠한 이유로든 최초 1회 풀스캔이 일어나 db buffer에
데이터를 모두 불러들었다면 계속해서 인덱스를 쓸필요가 있을까요?
(메모리로 다 처리 못할 정도로 데이터가 많은 경우가 있겠지만 이는 넘어가야 될 것같습니다.)
마. '라'의 경우와 같다면 행의 계층구조적인 재배치(순서 정렬) 및 필터링만 하면 되지 않을까요?
의문이 꼬리에 꼬리를 뭅니다. ^^
인덱스는 Start With 절에서도 탈 수 잇구요
Connect By 절에서도 탈 수 있지요.
결합인덱스(mgr, empno)를 만들면 더 효과가 있을것도 같구요.
그런데 여기까지는 10g 의 얘기구요. 테이블을 여러번 읽어야만 하므로
11g 부터는 앞서 언급했듯이 동작방식이 더 스마트하게 바뀐듯 하네요.
한번에 읽어 처리하려니 풀스캔이 나을 듯 합니다.
답변이 많은 도움되었습니다.
질문하면서 머리속이 정리가 많이 되네요. ^^;
) 버튼을 클릭하여 작성 하시면 됩니다.