oracle hierarchical query nocycle and connect by root

Question

Can somebody explain use of nocycle and connect by root clauses in hierarchical queries in oracle, also when we dont use 'start with' what is the order we get the rows, i mean when we don't use 'start with' we get lot many rows, can anybody explain nocycle and connect by root(how is different than start with?) using simple emp table, Thanks for the help

Answer 1

If your data has a loop in it (A -> B -> A -> B ...), Oracle will throw an exception, ORA-01436: CONNECT BY loop in user data if you do a hierarchical query. NOCYCLE instructs Oracle to return rows even if such a loop exists.

CONNECT_BY_ROOT gives you access to the root element, even several layers down in the query. Using the HR schema:

select level, employee_id, last_name, manager_id ,
connect_by_root employee_id as root_id
from employees
connect by prior employee_id = manager_id
start with employee_id = 100

     LEVEL EMPLOYEE_ID LAST_NAME                 MANAGER_ID    ROOT_ID
---------- ----------- ------------------------- ---------- ----------
         1         100 King                                        100
         2         101 Kochhar                          100        100
         3         108 Greenberg                        101        100
         4         109 Faviet                           108        100
...

Here, you see I started with employee 100 and started finding his employees. The CONNECT_BY_ROOT operator gives me access to King's employee_id even four levels down. I was very confused at first by this operator, thinking it meant "connect by the root element" or something. Think of it more like "the root of the CONNECT BY clause."