Home » SQL & PL/SQL » SQL & PL/SQL » Performance problem of connect by clause
Performance problem of connect by clause [message #220615] Wed, 21 February 2007 07:06 Go to next message
maxboom123
Messages: 9
Registered: August 2006
Junior Member
Hi All,
I'm having a query which contains a connect by in its where clause.
(where clause looks similer to
"where level>1
and p_id =1
connect by prior p_child = p_parent
starts with p_depend_id is null")

It takes 8 seconds to run this query in oracle 10g at the same time it takes only 2 sec in oracle 8i.

Has any one faced a similiar problem??. If so please share how you corrected it.

Thanks in advance........

Mx.

Re: Performance problem of connect by clause [message #220616 is a reply to message #220615] Wed, 21 February 2007 07:09 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member


It takes 8 seconds to run this query in oracle 10g at the same time it takes only 2 sec in oracle 8i.

Against the same data over the 2 database versions ?
Are all the indexes and table structures the same ?


If so , have you updated statistics recently ?

Re: Performance problem of connect by clause [message #220618 is a reply to message #220615] Wed, 21 February 2007 07:15 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Have you looked at the query's explain plan ?
Previous Topic: escape character for '&'
Next Topic: want to update any character of a field
Goto Forum:
  


Current Time: Thu Dec 08 16:19:11 CST 2016

Total time taken to generate the page: 0.10008 seconds