Detect loop in hierarchical data
703698May 27 2009 — edited Feb 3 2012Hi,
I have a table that stores hierarchical data structures, e.g.
id | value | parent
1 | 123 | null
2 | 333 | 1
3 | 334 | 1
4 | 111 | 2
5 | 666 | 6
6 | 777 | 7
7 | 888 | 5
I have a few questions about the hierarchical queries API in oracle10g:
1. How efficient are these queries? I intend to use it on a database with potentially millions of records (although possibly partitioned to bound the search space) by running a set of parallel stored procedures that repeatedly run and search for roots of trees and for each, retrieve its whole tree (in the same query). Does it sound reasonable?
2. I need to be able to detect loops (as between 5-6-7 above). Is there an efficient way of doing so?
Appreciate your comments!