Jerry,
We created views to look at our "tree data." This seems to work in most cases. When the views prove to be inefficient (Cartesian joins, etc.), we use tables. Of course, the downfall is that the tables have to be updated in order to get an accurate view of the tree. (Since I walked into this project after it was started, I did not have the opportunity to investigate the issue, so these methods may be discounted by others.)
Chuck Chauvin
Stone Carlie Consulting, L.L.C.
(cchauvin@stonecarlie.com)
-----Original Message-----
From: Jerry Kight [SMTP:Jerry_Kight@AONCONS.COM]
Sent: Wednesday, November 11, 1998 12:30 PM
To: Multiple recipients of list SQR-USERS
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;