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 iterationSo 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 be found here, in the SQL Language Reference, under the SELECT statement.
Remember, the key to understanding recursion is understanding recursion :-)