CODEWITHSUNDEEP
sqloracle-databaseconnect-by
Raghav
Raghav

Reputation: 11

CONNECT BY NOCYCLE generating millions of child nodes

I have a database where from_node and to_node are the columns. Trying to find all the reachable nodes from from_node to to_node. There are cycles. Few from_node using connect by nocycle, is generating millions of children nodes. How can this be resolved?

SELECT from_node,
       to_node,
       level
FROM PATH
START WITH from_node = input_var
CONNECT BY NOCYCLE PRIOR to_node= from_node;

Upvotes: 1

Views: 1253

Answers (1)

Del
Del

Reputation: 1599

Based on your description, I might look into using a recursive subquery factoring clause instead of a hierarchical query. This will more easily allow you to get rid of the cycles. This is because NOCYCLE doesn't actual stop cycles, but prevents Oracle from caring about them. This is an extract from the Oracle Documentation:

The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which rows contain the loop. Refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information.

Something like this recursive subquery may work better for you:

WITH path_search (from_node, to_node, path_level) AS
(
  SELECT p.from_node, p.to_node, 0 AS PATH_LEVEL
  FROM paths p
  WHERE p.from_node = 'A'
  UNION ALL
  SELECT p.from_node, p.to_node, ps.path_level+1 AS path_level
  FROM path_search ps
  INNER JOIN paths p ON ps.to_node = p.from_node
)
SEARCH DEPTH FIRST BY to_node SET order_col
CYCLE from_node SET is_cycle TO 'Y' DEFAULT 'N'
SELECT ps.from_node, ps.to_node, MIN(ps.path_level) AS MIN_DISTANCE
FROM path_search ps
WHERE is_cycle = 'N'
GROUP BY ps.from_node, ps.to_node
ORDER BY min_distance;

Here is an SQLFiddle with this solution (Link).

This will allow you to stop the recursion once a cycle is detected in a given path. However, as there still may be duplicate from and to nodes as the same path segment can be found on different paths, I added the grouping to only show the minimum level as it would be the best way to get to a given node.

Upvotes: 1

Related Questions