also do you have indexes on group_id,user_id and (especially) node_id.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:udlbd5e1sa7d92_at_corp.supernews.com...
>
> "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
>
>
>
Received on Fri May 10 2002 - 00:28:53 CDT
