Re: tree logic - connect by clause

"Mark" <markoos_at_hotmail.com> wrote in message news:ce165b7d.0205090809.aaf4f0f_at_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
I would first start with removing the redundant distinct in your in ( ) subqueries. The stuff between the parentheses is a set, and a set by definition has unique tuples.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address