Year: 2016

I’m tired of being limited to just DBA_OBJECTS as a test source for exploring a “filtered hierarchy query”. I found out how to generate unique names for each node. Now I just need to link them up…
The real struggle has been: where to start? I know that’s a vitally important issue based on my earlier experimentation. Vitally important or not, I have to get to the root of the issue..(Snicker; at least until the reality sets in…)
Here’s what I’ve got: the query so far (since I’m using a random number generator, your result sets will almost certainly be different). Here’s what the sub queries do:
1. set the number of records as a parameter.
2. generate the nodes, including the parent ID and name (“T”).
3. chain all the nodes together; this results in lots of records.
4. For each ID, choose the record with the longest chain.
First the SQL, then I’ll discuss the results for two sets of ten (10) records I’m “noodling through”. It doesn’t take a large data set to illustrate the issues.

The last three records look promising: there’s a clear loop-of-3, and one of those is marked as a leaf. So just choose that as the end-point?
No, not quite. The LEAF indicator is unreliable in loops. Look at the third and sixth records. Both are loops-of-1 (with itself), but only one is indicated as a leaf node…
In the first result set (above with the SQL), rows 3 and 5 point to each other. Neither is indicated as a leaf.
I could always “throw out” all the loops, but sometimes that’s a big number of records (not that it’s difficult to generate more). Still I’m wondering how to “break” the loops so they become usable.
Unfortunately, the CSV list of nodes is in different order, so it’s not like it is easy (except visually) to determine that 8,9,10 are together as a loop. Otherwise, I’d just take the maximum number in the loop as the start… or can I? I’d have to parse the numbers… not impossible, but annoying…