Oracle Scratchpad

June 30, 2009

Connect By

Filed under: Infrastructure — Jonathan Lewis @ 9:22 am BST Jun 30,2009

If you have to deal with hierarchical queries (or “connect by” queries, as they’re commonly known) you might be interested in this note on the OTN Forum where someone has a problem with repeated executions of the same query (same inputs, same results, same execution plan) operating at wildly different speeds with amazing changes in workload.

Ultimately the solution was the same as for the more obvious question: “why does my sort sometimes take much longer than usual?”  – shortage of memory (in this case a very low setting for the pga_aggregate_target).

This example is an interesting variation, though, as it demonstrates how the availability of resources can make Oracle choose to execute different parts of an execution plan – giving another reason for the “conditional plan” strategy I’ve described in the past.

There’s an interesting post on hierarchical queries on Christian Antognini’s blog (which I’ve also referenced from the OTN posting)


  1. On a slightly different note, I’ve always been mystified, although not too much, by apparent Oracle unwillingness to implement the standard Sql’99 recursive query (recursive WITH … UNION ALL). After all, in addition to DB2 and SQL Server, even Postgress and Teradata have the recursive WITH.

    On the face of it, both ways to recurs seem to be equally powerful, but the latter has the advantage of being standard.

    Comment by Val — July 6, 2009 @ 2:58 pm BST Jul 6,2009 | Reply

  2. Hi Jonathan,

    Thanks for answering my post on OTN.

    On my vacation in Switzerland I read ‘My family and other animals’ written by Gerald Durrell.
    One part reminded me of you when he wrote that his new teacher at Korfu must have been certainly a good teacher since he had a beard.
    You didn’t shave off yours, did you?

    regards Hans-Peter

    Comment by Hans-Peter — August 11, 2009 @ 6:09 am BST Aug 11,2009 | Reply

    • Hans-Peter,

      Gerald Durrell was one of my favourite authors in my youth – although I haven’t read any of his books recently.

      I have always felt that to be a truly excellent Oracle specialist you have to have a “full-set” (English Navy term for beard and moustache) – which is probably why Tom Kyte finally grew a set a couple of years ago.

      Comment by Jonathan Lewis — August 11, 2009 @ 8:29 pm BST Aug 11,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: