Oracle hierarchical query on non-hierarchical data
What is your expected maximum depth to reach any child node?
If it's relatively small, you could loop down, while checking for nodes you have already visited, in a manner something like this...
(Note, I'm not an Oracle expert so this is closer to pseudo code with a little real SQL mixed in)
CREATE TABLE myMap (parent INT, child INT);INSERT INTO myTable SELECT NULL, 2 FROM DUAL;WHILE (SQL%ROWCOUNT > 0)LOOP INSERT INTO myMap SELECT DISTINCT dataMap.parent, dataMap.child FROM myMap INNER JOIN dataMap ON myMap.child = dataMap.parent WHERE NOT EXISTS (SELECT * FROM myMap WHERE parent = dataMap.parent)END LOOP;
Depending on performance, you may also want a depth
field in myMap
; optimising the join so as to only join on the most recent nodes. This would imply two indexes; one for the JOIN (depth)
and one for the NOT EXISTS (parent)
.
EDIT
Added the DISTINCT key word, to avoid the following case...
- Node 2 maps to 3 and 4
- Nodes 3 and 4 both map to node 5
- All children of node 5 would now be processed twice
GROUP BY, or many other options, can be used to cater for this instead of DISTINCT. It's just that the NOT EXISTS on it's own is not sufficient.
I have not worked with this myself, but what about a CONNECT BY with the NOCYCLE option? That should stop travering the tree when it sees a loop. Oracle 11i definitely has that, I think it came in somewhere in the Oracle 10g period.
This might help until visited exceeds 4000 bytes. Cycles should not be possible but the line is there just as an example.
WITH descendants(node, lvl, pth, visited) AS ( SELECT child node, 1, cast(child as varchar2(4000)), '/'||listagg(child,'/') within group (order by child) over()||'/' FROM t where parent = 2 UNION ALL SELECT child, lvl+1, pth||'/'||child, D.visited||listagg(child,'/') within group (order by child) over()||'/' FROM T INNER JOIN descendants D ON T.parent = D.node WHERE D.visited not like '%/'||child||'/%' ) cycle node set cyc to '1' default '0' SELECT distinct node FROM descendants order by node ;