Recursive Subquery Factoring

OK, this post is partially for my benefit because I'm sure in future I'll need to re-think how this works - and I'll want the basic syntax on hand.

From 11g Release 2, the SQL WITH clause has been extended to allow recursive queries. This new syntax complies with the ANSI standards, as opposed to Oracle's CONNECT BY, START WITH keywords.

It's officially called "recursive subquery factoring", but it's also known as tree-walking or a hierarchical query.

Unfortunately in this simple example, the ANSI syntax is somewhat more verbose. I wonder if this evens out as the complexity of the query increases, or if the readability of the code is "scalable"?

-- 10g method
SELECT o.org_id, o.name, o.parent_org_id, level
FROM organisations o
CONNECT BY PRIOR org_id = parent_org_id
START WITH org_id = 1000;

-- 11 method
WITH org_sage (org_id, name, parent_org_id, reportlevel) AS
  (SELECT org_id, name, parent_org_id, 1 reportlevel
   FROM   organisations
   WHERE  org_id = 1000
   UNION ALL
   SELECT o.org_id, o.name, o.parent_org_id, reportlevel+1
   FROM  org_sage p, organisations o
   WHERE p.org_id = o.parent_org_id
)
SELECT org_id, name, parent_org_id, reportlevel
FROM org_sage;

Another unfortunate outcome is a quick test of throughput - 10000 iterations on my laptop gave the following respective timings.

.81 secs
.000081 secs per iteration
3.56 secs
.000356 secs per iteration

So it might be best to compare the two in your scenario/data, and consider the value of using the ANSI format in your case.

Further documentation can on hierarchical queries can found here, and in the SQL Language Reference, under the SELECT statement, looking at subquery factoring.

Remember, the key to understanding recursion is understanding recursion :-)