Oracle: Connect By Loop in user data

Question

I understand when a loop can occur in Oracle. Theoritically it says if a record is both parent to another node and at the same time it is child to it, then it can enter into a loop.

But I can't catch why my this particular query is running into a loop.

SELECT Empno, Ename, Job
FROM Emp
START WITH Empno = 7839
CONNECT BY PRIOR
Job='PRESIDENT'

Can someone please explain me how this data can result into a loop. I made a CONNECT_BY_ISCYCLE check and found that the record looping is KING(President). But I still don't understand how can it be possible, as KING is President and I don't see any way in which it is becoming both child and parent to any record in the table.

Please explain me, why this is an error and where is the loop?

Thanks in advance.

FYI, The table is the default EMP table in SCOTT user in Oracle.

Blaylock

Answer 1

To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT BY condition can contain other conditions to further filter the rows selected by the query.

If the CONNECT BY condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.

if there are no rows which satisfy START WITH and have Job = 'PRESIDENT' the loop will never occur (Oracle retrieves only START WITH rows)

if there is a row in the table which satisfies START WITH and has Job = 'PRESIDENT' the loop occurs in any case because:
1. Oracle finds all the rows which satisfy the START WITH (root rows).
2. For each row from p.1 Oracle scans the whole table for descendants. All rows (including the row from p. 1) satisfy the condition in CONNECT BY (because prior Job = 'PRESIDENT' is always true)
3. Obvious ...

Underhung

Hi Multisync, I understand the theory. What I can't understand is even if I have a single row with Job='PRESIDENT', I am getting the loop error. – Blaylock

@Blaylock "Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row in the table". Oracle scan the whole table when it looks for descendants – Underhung

Thanks for the answer. I got it now. – Blaylock

@Blaylock Thanks too. My answer wasn't correct at the begining – Underhung

Answer 2

Add the expression nocycle connect by nocycle

Pirog

Answer 3

SELECT Empno, Ename, Job
FROM Emp
START WITH Empno = 7839
CONNECT BY nocycle PRIOR
Job='PRESIDENT'

Milwaukee

Answer 4

In my case, it may be a little different with your case. But I hope it could also help others. Because my bossno data also is EMPNO in other records so it's running on a loop. I've add a condition to prevent the problem

SELECT PRIOR BOSSNO, BOSSNO, EMPNO 
FROM Emp 
WHERE A.EMPNO   <> '7839' -- Add it here
START WITH BOSSNO = 7839 
CONNECT BY PRIOR CONNECT BY PRIOR A.EMPNO  = A.BOSSNO

Interpleader