StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy Oracle connect by with nocycle follows root cycle
text
Body
copied!<p>Does anyone know why Oracle continues to follow a path beyond a cyclical loop when the cycle occurs at the top node (root node connected right back to root node)? More importantly, how to prevent it?</p> <p>I have Oracle 11g Release 2 (11.2) and I have been exploring hierarchical queries. I will build my question around the tree structure in figure 9-1 of the Oracle Database SQL Language Reference page 9-4</p> <p>I created a table structe for this tree using the concept of vendors and cusomers:</p> <pre><code> create table t ( vendor varchar2(3) , customer varchar2(3) ); insert into t values ( '1' , '2' ); insert into t values ( '2' , '3' ); insert into t values ( '2' , '4' ); insert into t values ( '4' , '5' ); insert into t values ( '4' , '6' ); insert into t values ( '1' , '7' ); insert into t values ( '7' , '8' ); insert into t values ( '1' , '9' ); insert into t values ( '9' , '10' ); insert into t values ( '10' , '11' ); insert into t values ( '9' , '12' ); commit; </code></pre> <p>The following select query traverses the tree with no problems:</p> <pre><code> select vendor, customer, level, connect_by_isleaf as isleaf, connect_by_iscycle as iscycle, connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path from t connect by nocycle vendor=prior customer start with vendor='1'; </code></pre> <p>Giving the results:</p> <pre><code>Vendor Cust Level Isleaf Iscycle Path 1 2 1 0 0 1 ~ 2 2 3 2 1 0 1 ~ 2 ~ 3 2 4 2 0 0 1 ~ 2 ~ 4 4 5 3 1 0 1 ~ 2 ~ 4 ~ 5 4 6 3 1 0 1 ~ 2 ~ 4 ~ 6 1 7 1 0 0 1 ~ 7 7 8 2 1 0 1 ~ 7 ~ 8 1 9 1 0 0 1 ~ 9 9 10 2 0 0 1 ~ 9 ~ 10 10 11 3 1 0 1 ~ 9 ~ 10 ~ 11 9 12 2 1 0 1 ~ 9 ~ 12 </code></pre> <p>I then complicated things by adding cycles to the structure. First a record for a vendor who sells to themselves…</p> <pre><code> --self cycle insert into t values ( '4' , '4' ); </code></pre> <p>and one for a vendor whos customer is the vendor of their vendor…</p> <pre><code> --ancestor cycle insert into t values ( '6' , '2' ); </code></pre> <p>Reexecuting the select query above results in the same output as above except Iscycle is 1 for row 3 and row 5 (Paths 1 ~ 2 ~ 4 and 1 ~ 2 ~ 4 ~ 6). Note that the CONNECT BY nomenclature flags the parent record of a cycle not the child record actually completing the cycle. (So I know 4 and 6 both cycle back to an ancestor but I don’t know WHICH ancestor.)</p> <p>Adding two more records creates a larger cycle across the branches of the original tree:</p> <pre><code> --cycle crossing branches of tree insert into t values ( '6' , '9' ); insert into t values ( '11' , '2' ); </code></pre> <p>Reexecuting the select query again gives the following output:</p> <pre><code>Vendor Customer Level Isleaf Iscycle Path 1 2 1 0 0 1 ~ 2 2 3 2 1 0 1 ~ 2 ~ 3 2 4 2 0 1 1 ~ 2 ~ 4 4 5 3 1 0 1 ~ 2 ~ 4 ~ 5 4 6 3 0 1 1 ~ 2 ~ 4 ~ 6 6 9 4 0 0 1 ~ 2 ~ 4 ~ 6 ~ 9 9 10 5 0 0 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 10 11 6 1 1 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11 9 12 5 1 0 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12 1 7 1 0 0 1 ~ 7 7 8 2 1 0 1 ~ 7 ~ 8 1 9 1 0 0 1 ~ 9 9 10 2 0 0 1 ~ 9 ~ 10 10 11 3 0 0 1 ~ 9 ~ 10 ~ 11 11 2 4 0 0 1 ~ 9 ~ 10 ~ 11 ~ 2 2 3 5 1 0 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3 2 4 5 0 1 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 4 5 6 1 0 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5 4 6 6 1 1 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6 9 12 2 1 0 1 ~ 9 ~ 12 </code></pre> <p>The output continues to be as expected. All cycles are flaged and the mapping stops when a cycle is encountered.</p> <p>Now the problem child… Let’s add a self cycle to the root node which is exactly the same as the first cycle created above with node 4; just for node 1.</p> <pre><code> insert into t values ( '1' , '1' ); </code></pre> <p>This time Oracle detects the cycle at node 1, as expected (first row is flagged with Iscycle set to 1); HOWEVER, it continues past this cycle and builds out the entire tree structure twice. Rows 2 through 21 are a duplication of rows 22 through 41 with the cycle of node 1 prepended onto the front of the path.</p> <pre><code>Vendor Customer Level Isleaf Iscycle Path 1 1 1 0 1 1 ~ 1 1 2 2 0 0 1 ~ 1 ~ 2 2 3 3 1 0 1 ~ 1 ~ 2 ~ 3 2 4 3 0 1 1 ~ 1 ~ 2 ~ 4 4 5 4 1 0 1 ~ 1 ~ 2 ~ 4 ~ 5 4 6 4 0 1 1 ~ 1 ~ 2 ~ 4 ~ 6 6 9 5 0 0 1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 9 10 6 0 0 1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 10 11 7 1 1 1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11 9 12 6 1 0 1 ~ 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12 1 7 2 0 0 1 ~ 1 ~ 7 7 8 3 1 0 1 ~ 1 ~ 7 ~ 8 1 9 2 0 0 1 ~ 1 ~ 9 9 10 3 0 0 1 ~ 1 ~ 9 ~ 10 10 11 4 0 0 1 ~ 1 ~ 9 ~ 10 ~ 11 11 2 5 0 0 1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 2 3 6 1 0 1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3 2 4 6 0 1 1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 4 5 7 1 0 1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5 4 6 7 1 1 1 ~ 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6 9 12 3 1 0 1 ~ 1 ~ 9 ~ 12 1 2 1 0 0 1 ~ 2 2 3 2 1 0 1 ~ 2 ~ 3 2 4 2 0 1 1 ~ 2 ~ 4 4 5 3 1 0 1 ~ 2 ~ 4 ~ 5 4 6 3 0 1 1 ~ 2 ~ 4 ~ 6 6 9 4 0 0 1 ~ 2 ~ 4 ~ 6 ~ 9 9 10 5 0 0 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 10 11 6 1 1 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 10 ~ 11 9 12 5 1 0 1 ~ 2 ~ 4 ~ 6 ~ 9 ~ 12 1 7 1 0 0 1 ~ 7 7 8 2 1 0 1 ~ 7 ~ 8 1 9 1 0 0 1 ~ 9 9 10 2 0 0 1 ~ 9 ~ 10 10 11 3 0 0 1 ~ 9 ~ 10 ~ 11 11 2 4 0 0 1 ~ 9 ~ 10 ~ 11 ~ 2 2 3 5 1 0 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 3 2 4 5 0 1 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 4 5 6 1 0 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 5 4 6 6 1 1 1 ~ 9 ~ 10 ~ 11 ~ 2 ~ 4 ~ 6 9 12 2 1 0 1 ~ 9 ~ 12 </code></pre> <p>Why isn’t the 1-1 cycle treated the same as the 4-4 cycle? What am I missing?</p> <p>To mitigate against this I added an additional condition on the CONNECT BY clause requiring that the customer not be ‘1’. </p> <pre><code> select vendor, customer, level, connect_by_isleaf as isleaf, connect_by_iscycle as iscycle, connect_by_root vendor||sys_connect_by_path(customer,' ~ ') as path from t connect by nocycle vendor=prior customer and customer<>'1' start with vendor='1'; </code></pre> <p>Ironically, all this did was REMOVE the cycle flag from row one. </p> <p>Any help would be appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload