Recursive Query without using connect_by
411538Dec 19 2003 — edited Jun 7 2004Hi All,
I have to find all the nodes related to a given node in a huge database table(billions of rows). Each node contains the following fields:
nodeID
TargetID
SourceID
TypeOfNode
Some XML metadata
The nodeID is the id for each node. My task is given a nodeID say 10 would be to retrieve this node from the database get the TargetID of this node. Then find all the nodes in the database that have their sourceID or targetID that equals this nodes TargetID. I have to do that recursively on each node that I found in the last query till I have the complete graph of all the nodes that are related to the node provided.
The method of how I am currently doing it is that I use the "connect_by" query in Oracle to find the tree of relationships in one direction then take the leaves of the tree and do a "connect by" in the opposite direction if I find any new roots I do a query in the reverse direction ..I keep doing that till I find no new roots or leaves. This works when the data is small but with the size of the data that I am quering at, I genrally end up with a OutOfMemory error and/otherwise get data back in about 4-5 days.
I was looking over the following SQL99 facility that I was hoping to use in order to get a controlled recursion on the data to do a transitive closure for graphs.
According to Stefan Wagner
"The last ISO standard for SQL, called SQL99 , provides a facility called recursive query. Writing a recursive query involves writing the query expression that one wants to recurse on and giving it a name as a temporary view, then using that name in an associated query expression.
WITH RECURSIVE
Q1 AS SELECT ... FROM ... WHERE ...,
Q2 AS SELECT ... FROM ... WHERE ...
SELECT ... FROM Q1, Q2, WHERE ... "
Do you know if we can do this in Oracle 9i or 10g. The reason why I am not really fond of "connect by" is that it allows us a very limited control on the recursion specially the type we need for a transitive closure for graphs.
I would be really gratful if you could provide some tips on how to go about solving this problem.
Thanking You,
Sumit