yet another hierarchy model

Here is one more way to represent hierarchy: just store depth-first sequence number and level (simple, indeed!).

1. To find out the node parent, we need to select all the nodes on upper level and, then, choose the one with maximum sequence number which is above our node.

0 SELECT STATEMENT
1 SORT ORDER BY

2     NESTED LOOPS
3       NESTED LOOPS
4         NESTED LOOPS
5           TABLE ACCESS FULL FND_TABLES
6           TABLE ACCESS BY INDEX ROWID FN
7             INDEX RANGE SCAN FND_PRIMARY
8         TABLE ACCESS BY INDEX ROWID FND_
9           INDEX RANGE SCAN FND_PRIMARY_K
10       TABLE ACCESS BY INDEX ROWID FND_CO
11         INDEX UNIQUE SCAN FND_COLUMNS_U1

For node #11 we choose among 3 and 10 in favor of 10.

2. To find out all the nodes in a subtree, we find a next node on the same level and the next node on the parent level, choose the closest of the two and, then, select all the nodes between the given node and the chosen one.

For node #4 we pick up nodes 8 and 10, and select every node between 4 and 8 (exclusive).

3. Finding all node's children is just filtering out of case #2 the nodes with proper level.

4. Finding a path to the root is just selecting all the predecessor nodes, grouping them by level and extracting maximum sequence numbers of each group.

For node #8 we partition the range 0-8 into groups: 1,2,3,4&8,5&6,7, so that the path becomes: 1,2,3,8.

Did I just reinvented the wheel? Received on Thu Oct 11 2001 - 21:31:00 CEST