Home » SQL & PL/SQL » SQL & PL/SQL » Help with storing Hierarchical data using Nested Intervals and Matrix Encoding (Implementation problems buiding a solution based on Vadim Tropashkos Ideas)
Help with storing Hierarchical data using Nested Intervals and Matrix Encoding [message #274365] Mon, 15 October 2007 17:58
Messages: 1
Registered: October 2007
Junior Member
I have been working on an implementation of Nested Invervals to store some hierarchial data.

The code that I am writing is based on Vadim Tropashkos book SQL Design Patterns.

I have run into a number of problems with the code in the book and I am wondering if anyone has worked through chapter 5 of this book and has tried to implement Nested Intervals using the Matrix encoding.

I have had several problems with the accuracy of the code in the book. I can not get some of the proposed queries proposed in the book to work. For example:

To query for descendants:
Select descendant.*
From MatrixTreeNodes descendant,MatrixTreeNodes node
Where descendant.a11*node.a21 >= descendant.a21*node.a11 and
descendant.a11*node.a22 >= descendant.a21*node.a12
and node.name=some name

This does not work for me. It returns all nodes in the tree. I had to go back to this query in the book:

select descendant.*
from MatrixTreeNodes descendant, MatrixTreeNodes node
where descendant.a11/descendant.a21 between node.a11/node.a21
and (node.a11-node.a12)/(node.a21-node.a22)
and node.name = … -- predicate uniquely identifying a node

The first query is supposed to be equivalent to the second query with all the expressions rewritten without divisions.

When I went through the process of rewritting the query to get rid of divisions, I come up with something completely different:

tblSiteLogonHierarchy parent
join tblSiteLogonHierarchy decendants
on decendants.a11 * parent.a21> decendants.a21 * parent.a11 and
((decendants.a11 * parent.a21) -(parent.a22*decendants.a11))
((parent.a11 * decendants.a21) -(parent.a21*decendants.a21))

parent.lSiteLogonID = @lSiteLogonID

This query works but leads me to the problem that has sunk the whole concept for me.

I know this doesnt concern oracle types, when I try to implement this on SQL Server
WHen you get up to a large tree, conditions like decendants.a11 * parent.a21> decendants.a21 * parent.a11 blow up because of over flow. I am using values larger than 9223372036854775807 which is the maximum value that can fit into a bigint.

I need to implement this on both SQL Server and Oracle.

Anyway, has there been other attempts at similar encodings for hierarchial data.

I can always go back to materialized path or adjacency list. Dont want to because its not as interesting.

Is there something similar where the numbers do not grow as fast?
Previous Topic: Utl__File
Next Topic: User scripts
Goto Forum:

Current Time: Sat Feb 25 14:34:44 CST 2017

Total time taken to generate the page: 0.05171 seconds