【SQL 學習】層次化查詢之CONNECT BY 和 START WITH

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)