title: Why Oracle connect by with nocycle follows root cycle
body: <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&lt;&gt;'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>