Re: Slow connect by prior ... start with subquery in 9i

From: Andrew Stewart <cdos_at_claudius.demon.co.uk>
Date: Thu, 01 Apr 2004 11:29:32 +0100
Message-ID: <rhpn60lhim0ic0r44lit3b0ipnhtepq9ck@4ax.com>

On Wed, 31 Mar 2004 23:22:59 GMT, "VC" <boston103_at_hotmail.com> wrote: Hello,

Thanks for that. I'd picked up on some of these threads (and some stuff on metalink).

Generally there seems to be a trade off - sorting and cpu go up in 9, consistent reads go down. Slower but more scalable, fine.

However with a START WITH subquery I'm getting the worst of both worlds. Slower AND more consistent reads. The subquery block that used to run once per query is now revisited, correlated subquery style, once per result set row, with potentially disastrous results.

>Hello Andrew,
>
>The 'connect by' in 9i, in my experience, has performance problems (in
>comparison to 8i). Please see my earlier posting on the subject:
>
>http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=31e0625e
>.0310221845.21354d7c%40posting.google.com&rnum=2&prev=/groups%3Fq%3Dconnect%
>2520by%25208i%2520vc%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26sa%3DN%26
>tab%3Dwg
>
>VC
>
Received on Thu Apr 01 2004 - 04:29:32 CST