Re: Using Materialized Path to create a paths table
Date: 26 May 2006 06:58:42 -0700
Message-ID: <1148651922.551570.235450_at_38g2000cwa.googlegroups.com>
--CELKO-- wrote:
> Get a copy of TREES & HIERARCHIES IN SQL and look up the Nested Sets
> model. Much faster, easier to use in calculations, etc. Avoid path
> enumeration and adjacency list models.
I had a look at nested sets, but it seems that for large trees (maybe 100,000 nodes), the cost of renumbering the left and right pointers would be expensive if an insert required complete tree renumbering. Inserting NodeDepth rows in the transitive closure table for each new row is very simple.
I am doing the following:
finding all ancestors
finding all descendants
finding ancestors + descendants (UNION of two queries above)
and the performance is excellent using this transitive closure (path to ancestors) table. I previously read about trees in SQL, and I am slightly curious that this approach was not much discussed, as against the frequent discussion of adjacency list, materialized path and of nested sets.
I might have missed something, but it seems that using this transitive closure table is easy to maintain and has given very pleasing performance. Could you say what is bad about it? Received on Fri May 26 2006 - 15:58:42 CEST