StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>From documentation on <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/pseudocolumns001.htm" rel="noreferrer"><code>CONNECT_BY_ISCYCLE</code></a>:</p> <blockquote> <p>The <code>CONNECT_BY_ISCYCLE</code> pseudocolumn returns <code>1</code> if the current row has a child which is also its ancestor</p> </blockquote> <p>and that on <a href="http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_10002.htm#BABFFJFD" rel="noreferrer"><code>CYCLE</code></a>:</p> <blockquote> <p>A row is considered to form a cycle if one of its ancestor rows has the same values for the cycle columns.</p> </blockquote> <p>In your example, row <code>2</code> does have a child which is also its ancestor, but its <code>id</code> has not been returned yet.</p> <p>In other words, <code>CONNECT_BY_ISCYCLE</code> checks the <strong>children</strong> (which are yet to be returned), while <code>CYCLE</code> checks the <strong>current row</strong> (which is already returned).</p> <p><code>CONNECT BY</code> is row based, while recursive <code>CTE</code>'s are set-based.</p> <p>There is no concept of a "child" in a recursive <code>CTE</code>. It's a set based operation which can yield results completely out of the tree. Generally speaking, the anchor part and the recursive part can even use the different tables.</p> <p>Since recursive <code>CTE</code>'s are <em>usually</em> used to build hierarchy trees, <code>Oracle</code> decided to add a cycle check. But due the set-based way the recursive <code>CTE</code>'s operate, it's generally impossible to tell will the next step generate a cycle or not.</p> <p>To perform the "next" step, the whole "current" set needs to be available, but to generate each row of the current set (which includes the cycle column) we just need to have the results of the "next" operation. It's not a problem with a single row (like in <code>CONNECT BY</code>), but it is a problem with a set as a whole.</p> <p>Didn't look into <code>Oracle 11</code> yet, but <code>SQL Server</code> implements recursive <code>CTE</code>'s by just hiding a <code>CONNECT BY</code> behind them, which requires placing numerous restrictions (all of which effectively forbid all set-based operations).</p> <p><code>PostgreSQL</code>'s implementation, on the other hand, is truly set-based.</p> <p>As was mentioned before, <code>MySQL</code> does not implement <code>CTE</code>'s at all (it does not implement <code>HASH JOIN</code>'s or <code>MERGE JOIN</code>s as well, only the nested loops, so don't be surprised much).</p> <p>Ironically, I received a letter today on this very subject, which I will cover in my blog.</p> <p><strong>Update:</strong></p> <p>Recursive <code>CTE</code>'s in <code>SQL Server</code> are no more than <code>CONNECT BY</code> in disguise. See this article in my blog for shocking details:</p> <ul> <li><a href="http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/" rel="noreferrer"><strong>SQL Server: are the recursive CTE’s really set-based?</strong></a></li> </ul>

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