December 18th, 2003, 04:58 PM
Recursive Query without using connect_by
I am trying to do a recursive query to the database without using connect_by. The reason I am not so fond of connect_by is that it does not give me much control over the recursion.
Ok before I say anything further I must tell you what I am trying to do. Here is the story in short:
I have to find all the nodes related to a given node in a huge database table(transitive closure on a graph) . Each node contains the following fields:
Some XML metadata
The RelationshipID is the id for each node. My task is given a RelationshipID 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.
It would be really great if you could provide me with some ideas.
December 18th, 2003, 09:18 PM
Do you have indexes on TargetID and SourceID?
Oracle has been supporting a tree structure within a table for a long while, but after all a tree as data structure is supported just as an extension to the relational model.
XML document on the other hand is fundamentally built as a tree.
So, maybe you'll consider exporting this table to an XML document and operate on it via XML technologies, such as XSLT and XPath.
In Oracle 9i you can accomplish this export via:
select dbms_xmlgen.getxml('select * from <table_name>') from dual;
which will return the result set as an XML document.
You can also look into this Oracle dbms_xmlgen package and see whether it has a mechanism to operate better on your table after it has been converted to an XML document.
December 19th, 2003, 12:04 PM
Please could you explain your answer in a little more detail...from what I get of it is that I should convert my table that can have over a billion rows to XML and then probably use a SAX parser to parse it & use the events from the SAX parser to do my computation rather than the direct resultset that I would have otherwise get from my query..this seems to me as an added expense rather than as a saving...Please correct me if I am wrong...
December 20th, 2003, 02:50 AM
Have you tried writing your own pl/sql proc to traverse it?