#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    4
    Rep Power
    0

    Lightbulb Recursive Query without using connect_by


    Hi,

    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:
    RelationshipID
    TargetID
    SourceID
    Type
    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.

    Thank You,
    Sumit
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    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.

    Good Luck,
    Dan
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    4
    Rep Power
    0
    Hi Dan,
    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...

    Thank You,
    Sumit
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    Have you tried writing your own pl/sql proc to traverse it?

IMN logo majestic logo threadwatch logo seochat tools logo