Re: Sorting CONNECT BY query in Oracle 8.1

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 02 Oct 2003 12:54:11 GMT
Message-ID: <TdVeb.133680$bo1.26224@news-server.bigpond.net.au>

"Dave Hau" <davehau_nospam_123_at_nospam_netscape.net> wrote in message news:3F7B28B9.8060001_at_nospam_netscape.net...
> Hi Maoz,
>
> I suggest just sorting by LEVEL (or lev in your query) instead of using
> ROWNUM. I don't know if that'll speed up the query but you can try.
>
> 9i allows using CONNECT BY with a join query. So if you upgrade to 9i,
> you don't need to use a subquery and can do the entire operation in a
> single select statement.
>

Hi Dave,

In theory yes but unfortunately in practice we've experienced a number of problems with 9i joins with connect by. The queries work in that they return the correct results but the execution plans the CBO comes up with are atrocious. A 10053 trace suggests that the most obvious of indexes are totally ignored and very expensive hash joins and FTS result on both "sides" of the connect by pump.

However, re-writing the query with a single inline view containing the join executes perfectly with the expected execution plan and in a tiny fraction of the time.

It's reproducible and currently sitting in someone's intray at Oracle Support :)

Cheers

Richard Received on Thu Oct 02 2003 - 07:54:11 CDT