[comp.databases] ORACLE: Connect BY/Start With

psalvini@maytag.waterloo.edu (Paul Salvini) (01/16/91)

A few days ago I asked out possible clues for my bad performance relating
to connect by / start with clause. In a subsequent post I stated that
my problem was related to Oracle functions placed on the keys negating
the use of indexes.

Well, netters asked for a summary of responses...I only had 1 but I thought
it did bring up some good points, so here it is....
===============
From rubi@lgc.com  Thu Jan 10 10:35:30 1991
Subject: CONNECT BY
To: psalvini@maytag
Date: Thu, 10 Jan 91 9:34:13 CDT
X-Mailer: ELM [version 2.2 PL10]
Status: OR

Hi Paul,

I saw your request on the net for help on Oracle's
SQL CONNECT BY function. I played around with the
CONNECT BY function and found several things
affect the performance of CONNECT BY queries:

	1. Performance appears to be improved by 
	   creating a separate index for the 
	   parent and a separate index for the child.

	2. Of the Oracle toolset, Pro*C provided the
	   best performance followed by SQL*Plus. If
	   possible, use user exits in SQL*Forms. Do
	   not use SQL*Report (RPT/RPF). It is dramatically 
	   slower than the other tools. (I didn't test
	   SQL*ReportWriter but it doesn't impress me as
	   being a real speed burner.)

	3. I found that dynamically creating a temporary table 
	   to store the query results helped to improve overall
	   performance. The index on this table should be built
	   after the data has been loaded.

	4. The deeper the hierarchy, the slower the query 
	   runs.

In addition to the above, check out general performance     
issues (if you have not already done so) such as:

	1. Is the data in one extent? Are each of the indexes 
	   in one extent?

	2. Is the tablespace for the hierarchical data sitting
	   on a heavily used disk? Then move the files to a
	   quieter disk. 

	3. Is it a large table(s)? Maybe the data should be
	   striped over several disks.

	4. If using Unix, look at placing the tablespace files 
	   for the hierarchical data on raw disks or partitions.

	5. Can the number of buffers in the SGA be increased, etc?

Look at the ORACLE RDBMS Performance Tuning Guide for more information.
==============
-- 
===============================================================================
Paul A. Salvini                                            (519) 884-1973 x2913
Wilfrid Laurier University            ** INTERNET: psalvini@maytag.UWaterloo.ca 
Department of Computing Services