Re: How to improve query performance with ,,connect by" clause?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 14 Dec 2006 06:19:33 -0800
Message-ID: <1166105973.821063.216330@16g2000cwy.googlegroups.com>

On Dec 14, 4:50 am, Dirk Gomez <use..._at_dirkgomez.de> wrote:
> > I use CONNECT BY clause but it performs very slow though I use indexes.
> > Any suggests how to speed up the performance?Please post at least the query and the query plan.

Yes, posting the actual SQL and the explain plan would be a good step.

In the example of the EMP table where empno and manager can be used in a connect by relationship you would generally want an index not just on empno but also on manager. If you have both indexes and the stats are current then the CBO should be able to determine if the indexes or a hash join is the better option.

Use of the 9i and up ability to use a connect by clause and perform a to the same table would potentially complicate the SQL but just tune the connect and tune the join.

Take a look at the amount of data that you have to process and then estimate the run time and compare that to what you are getting. This will help limit your expectations to reality.

HTH -- Mark D Powell -- Received on Thu Dec 14 2006 - 08:19:33 CST