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 :-)

Apex Listener has arrived

For those of you who've missed it amongst all the Apex4.0 hubbub, the Apex Listener that was originally mentioned as being a separate release, is now available.

I've finally managed to get it running on my new Windows 7 64 bit laptop, but that was only after fiddling with the pre-release version and having a few other semi-related difficulties.

I've gone with Glassfish as my HTTP server, using Oracle Glassfish Server 3.0.1

There is now a dedicated OTN forum for the Apex Listener, and I've added forum entries on these problems - which I'm sure along with other little nagging things - the Oracle development will get onto with Glassfish as time goes on.

1) My URL to open Apex Builder requires the slash at the end, otherwise no deal
http://localhost:8080/apex40/

2) Unfortunately when I try to re-visit my listener configuration page
http://localhost:4848/apex40/listenerConfigure/
I get the following error
/apex40/listenerConfigure//index.jsf not found

I'm fairly happy with the set-up, it was relatively easy to do in the end and the new documentation accompanying the release is vastly improved - but Tomcat is no longer supported, only OC4j, WLS and Glassfish.

So now to continue exploring Apex4.0

I must say I'm coming across some improvements to the documentation. Apparently when creating new workspaces for existing schemas, Apex4.0 no longer grants any roles.
From there I noticed that there is a new glossary describing item types. I think this along with the Available Conditions appendix is the sort of documentation that is needed to assist people new to Application Express - that and the context sensitive help within the builder, that is sometimes a little lacking.

I confidence that over time, like all Oracle products, this will improve. I wonder what they're thinking about for the next release...

Friday Musings

I think it may have been Tom Kyte that originally got me onto Seth Godin, but Seth's latest post caught my eye. I'm furiously finishing off my latest presentation, while trying (and failing) fire up a HTTP server on my new laptop.


A slide every 12 seconds. 200 slides in all.

I tend to read most of his. They're succinct, precise, thought provoking. Creating a 200 slide presentation for a 40 minute block is quite a feat. I know Connor McDonald to have handed a stunned HR girl an eighty slide presentation for a 5 minute segment. He assured her it will take five minutes. It was a stunning presentation. His conference presentations are deserved winners.

I've tinkered with his style, which he picked up long ago from I can't remember where and made it his own. I've been trying to add in that style in my latest masterpiece, balance it with regular diagrams and code segments - which normally slow things down a touch.

If you can make it...
Apex and Oracle Text - Advanced Indexing Techniques Integrated with Application Express
From 16:15-1800, 14th Julye - Central TAFE, 140 Royal Street, East Perth
RSVP: WA AUSOUG President