Re: Performance issues with connect by clause with Trees

["Nasir, Jusuf" <Jusuf.Nasir@TRANSAMERICA.COM>]

An index on START_WITH and CONNECT _BY clause would definitely help the
performance.

Jusuf
x5837

> -----Original Message-----
> From: Jerry Kight [SMTP:Jerry_Kight@AONCONS.COM]
> Sent: Wednesday, November 11, 1998 11:12 AM
> To:   Discussion of SQR,              SQRIBE Technologies's database r
> Cc:   Nasir, Jusuf
> Subject:      Performance issues with connect by clause with Trees
>
> I have a very rudimentary tree set up using tree-manager in Peoplesoft
> (Oracle 7)
>
> When I use the "connect by" clause with a "start with" as the first
> tree_node_num in the tree, it takes a couple of hours to run.
>
> Any ideas on how performance can be tuned?
>
> SELECT  SETID,
> TREE_NAME,
>  EFFDT,
> TREE_BRANCH,
> TREE_NODE_NUM,
> TREE_NODE,
> TREE_LEVEL_NUM,
> TREE_NODE_TYPE,
> PARENT_NODE_NUM,
> TREE_NODE_NUM_END
> FROM PSTREENODE
>     WHERE   SETID = 'ACSHR'
> START WITH  TREE_NODE_NUM = 1
> CONNECT BY PRIOR  TREE_NODE_NUM =  PARENT_NODE_NUM;