"Richard Foote" wrote in message news:...
> "Dave Hau" wrote in message
> news:3F7B28B9.8060001@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
>

Hello,

We are generally unhappy with the hierarchical query implementation in
9i as well as with the new supposedly useful function
sys_connect_by_path.

Whilst the latter can be easily programmed in PL/SQL, the former is
just not as performant as the old 8i 'connect by' implementation and
one cannot do much about it.

We experienced the following problems on trees with more than 100,000
nodes:

1. The 9i 'connect by' is 2-3 times slower than the 8i one. Enabling
the old 8i behaviour sometimes cures the problem but then you cannot
use 'sys_connect_by_path'.

2. 'sys_connect_by_path' dies on a simple query like this:
select count(*) from (select sys_connect_by_path employee_id,',') from
employees
start with employee_id=100
connect by prior employee_id=manager_id).

('employees' is the standard demo table in 'HR')
This (mis)behaviour can be seen in 9.2.0.1 through 9.2.0.4 and across
various platforms (Solaris, Linux, Windows).

All this is quite frustrating as the Oracle support is close to being
completely useless (I submitted all the bugs, of course).

We even thought of changing the tree representation from the adjacency
list to some other encoding thus avoiding the 'connect by' entirely.
Unfortunately, this is not an option since our 'trees' are actually
DAGs and the AL seems to be the only practical representation in the
RDBMS.

VC.