tree logic - connect by clause

From: Mark <markoos_at_hotmail.com>
Date: 9 May 2002 09:09:57 -0700
Message-ID: <ce165b7d.0205090809.aaf4f0f@posting.google.com>

I have to build a tree in a web application. Each user may have different access permissions to the same tree. The complication is that the user has to see all of their nodes, plus any nodes higher in the tree. They can't click on any nodes they don't have access to, but they still have to be able to see them. I need to get a query that is sorted in the right order of the tree. We're on 9i so we have the ORDER SIBLINGS BY clause, but I still can't get this to work in under 5 seconds, which is too slow.

An query that is close but not quite, as it orders all alphabetically:

SELECT DISTINCT NODE_ID
FROM portal.t_NODE
START WITH node_ID IN (

   SELECT DISTINCT N.NODE_ID
   FROM PORTAL.T_ACCESS A, PORTAL.T_NODE N,    PORTAL.T_USER_IN_GROUP UIG
   WHERE UIG.USER_ID = 4004

   AND     A.GROUP_ID = UIG.GROUP_ID  
   AND     A.NODE_ID = N.NODE_ID

)
CONNECT BY node_ID = PRIOR parent_node --connect up the tree

And a working (but inefficient and way too slow) query is:

SELECT NODE_ID, PARENT_NODE, NAME
FROM portal.t_NODE
WHERE node_ID IN (

        SELECT DISTINCT NODE_ID
        FROM portal.t_NODE
        START WITH node_ID IN (
           SELECT DISTINCT N.NODE_ID
           FROM  PORTAL.T_ACCESS A, PORTAL.T_NODE N,
PORTAL.T_USER_IN_GROUP UIG
           WHERE   UIG.USER_ID = 4004  
           AND     A.GROUP_ID = UIG.GROUP_ID  
           AND     A.NODE_ID = N.NODE_ID
        )
        CONNECT BY node_ID = PRIOR parent_node --connect up the tree

    )
START WITH parent_node IS NULL
CONNECT BY PRIOR node_ID = parent_node
ORDER SIBLINGS BY name

Any thoughts on how to get a sorted tree efficiently?

TIA
-MM Received on Thu May 09 2002 - 11:09:57 CDT