SQL> select employee_id ,manager_id ,first_name ,last_name
2 from emp
3 start with employee_id =1--- 定义层次化查询的起点
4 connect by prior employee_id = manager_id;指定父行与子行之间的关系。也就是父节点的employee_id 等于 子节点的manager_id
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- --------------- ----------------
1 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn
已选择13行。
执行计划
----------------------------------------------------------
Plan hash value: 1213993571
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 234 | 3 (0)| 00:00:01 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL | EMP | 13 | 234 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("MANAGER_ID"=PRIOR "EMPLOYEE_ID")
filter("EMPLOYEE_ID"=1)