대용량 데이터베이스솔루션 2 (2009년)
1. 순환(Recursive)관계 SQL : 계층형 쿼리
{section}
{column:width=10}
SQL> SELECT LEVEL lv
2 , LPAD(' ', (LEVEL-1) * 2) || ename ename
3 , deptno
4 FROM emp
5 START WITH mgr IS NULL -- 계층쿼리의 루트 지정
6 CONNECT BY PRIOR empno = mgr -- 상위(PRIOR) 와 하위간의 관계 지정
7 ;
LV ENAME DEPTNO
---------- -------------------- ----------
1 KING 10
2 JONES 20
3 FORD 20
4 SMITH 20
2 BLAKE 30
3 ALLEN 30
3 WARD 30
3 MARTIN 30
3 TURNER 30
3 JAMES 30
2 CLARK 10
3 MILLER 10
12 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3364448299
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 204 | 3 (0)| 00:00:01 |
|* 1 | CONNECT BY WITH FILTERING| | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | | | | |
| 5 | CONNECT BY PUMP | | | | | |
| 6 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MGR" IS NULL)
2 - filter("MGR" IS NULL)
4 - access("MGR"=NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35 consistent gets
0 physical reads
0 redo size
795 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
12 rows processed
{column}
{column}{column:width=80}{column}
{section}
2. 조인집합에 대한 순환(Recursive) 전개
- 조인집합에 대한 계층형 쿼리 실행 시 에러 발생(8i까지)
{section}
{column:width=10}
SQL> SELECT LEVEL lv
2 , LPAD(' ', (LEVEL-1) * 2) || e.ename ename
3 , d.dname
4 FROM emp e, dept d
5 WHERE e.deptno = d.deptno
6 START WITH e.mgr IS NULL
7 CONNECT BY PRIOR e.empno = e.mgr
8 ;
LV ENAME DNAME
---------- -------------------- ----------------------------
1 KING ACCOUNTING
2 JONES RESEARCH
3 FORD RESEARCH
4 SMITH RESEARCH
2 BLAKE SALES
3 ALLEN SALES
3 WARD SALES
3 MARTIN SALES
3 TURNER SALES
3 JAMES SALES
2 CLARK ACCOUNTING
3 MILLER ACCOUNTING
12 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3513732447
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 360 | 4 (0)| 00:00:01 |
|* 1 | CONNECT BY WITH FILTERING | | | | | |
|* 2 | FILTER | | | | | |
| 3 | COUNT | | | | | |
| 4 | NESTED LOOPS | | 12 | 360 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 8 | HASH JOIN | | | | | |
| 9 | CONNECT BY PUMP | | | | | |
| 10 | COUNT | | | | | |
| 11 | NESTED LOOPS | | 12 | 360 | 4 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 15 | COUNT | | | | | |
| 16 | NESTED LOOPS | | 12 | 360 | 4 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."MGR" IS NULL)
2 - filter("E"."MGR" IS NULL)
7 - access("E"."DEPTNO"="D"."DEPTNO")
8 - access("E"."MGR"=NULL)
14 - access("E"."DEPTNO"="D"."DEPTNO")
19 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
102 consistent gets
0 physical reads
0 redo size
795 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
12 rows processed
{column}
{column}{column:width=80}{column}
{section}
2. 조인집합에 대한 순환(Recursive) 전개 오류 해결
{section}
{column:width=10}
SQL> SELECT e.lv
2 , LPAD(' ', (e.lv - 1) * 2) || e.ename ename
3 , d.dname
4 FROM (SELECT LEVEL lv
5 , ename
6 , deptno
7 FROM emp
8 START WITH mgr IS NULL
9 CONNECT BY PRIOR empno = mgr
10 ) e, dept d
11 WHERE e.deptno = d.deptno
12 ;
LV ENAME DNAME
---------- -------------------- ----------------------------
1 KING ACCOUNTING
2 JONES RESEARCH
3 FORD RESEARCH
4 SMITH RESEARCH
2 BLAKE SALES
3 ALLEN SALES
3 WARD SALES
3 MARTIN SALES
3 TURNER SALES
3 JAMES SALES
2 CLARK ACCOUNTING
3 MILLER ACCOUNTING
12 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1913249162
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 552 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 12 | 552 | 4 (0)| 00:00:01 |
| 2 | VIEW | | 12 | 396 | 3 (0)| 00:00:01 |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN | | | | | |
| 7 | CONNECT BY PUMP | | | | | |
| 8 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | EMP | 12 | 204 | 3 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("MGR" IS NULL)
4 - filter("MGR" IS NULL)
6 - access("MGR"=NULL)
11 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
49 consistent gets
0 physical reads
0 redo size
795 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
12 rows processed
{column}
{column}{column:width=80}{column}
{section}
About Doc.
- 최초작성자 : 기민용
- 최초작성일 : 2009년 5월 15일
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션2를 참고했습니다.*
"구루비 데이터베이스 스터디모임" 에서 2009년에 "대용량 데이터베이스 솔루션 2" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/2538
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.